#coding=utf-8
import copy
import cPickle
import dbmngr
from myLogger import *
import datetime
import kit
import config

# WareHouse whList[]
##def insertSql(table, filedList):
##    str1 = 'insert into %s (' %(table) 
##    str2 = 'values('
##    param = []
##    
##    for i in range(len(filedList)):
##        str1 += filedList[i][0]
##        str1 += ','
##        str2 += '%s,'
##        param.append(filedList[i][2])
##    
##    sql = str1[0:-1] + ')' + str2[0:-1] + ')'
##    
##    print u'执行sql'
##    print sql
##    print param
##           
##    return dbmngr.dbmngr.execute(sql, param)

def insertSql(table, feilds, vals):
    str1 = 'insert into %s (' %(table) 
    str2 = 'values('
    
    for i in range(len(feilds)):
        str1 += feilds[i]
        str1 += ','
        str2 += '%s,'
    
    sql = str1[0:-1] + ')' + str2[0:-1] + ')'
    
    print u'执行sql'
    print sql
    print vals
    
    return dbmngr.dbmngr.execute(sql, vals)
    
##def createObj(className):
##    print u'createDataoObj %s' %(className)
##    if (className == CDataCategory.__name__):
##        return CDataCategory()
##    elif (className == CDataProduct.__name__):
##        return CDataProduct()
##    else:
##        print u'Unkonwn Class %s' %(className)
##        return None
        
##def selectAllSql(table, dataList, className):
##    sql = u'select * from '+table
##    cnt = dbmngr.dbmngr.execute(sql)
##    ret = dbmngr.dbmngr.cursor.fetchall()
##
##    for item in ret:
##        new = createObj(className)
##        for j in range(len(item)):
##            new.m_field[j][2] = item[j]
##        dataList.append(new)

##def selectAllSql(table, dataList, classType):
##    sql = u'select * from '+table
##    cnt = dbmngr.dbmngr.execute(sql)
##    ret = dbmngr.dbmngr.cursor.fetchall()
##
##    for item in ret:
##        #new = createObj(className)
##        new = classType()
##        for j in range(len(item)):
##            new.m_fieldVals[j] = item[j]
##        dataList.append(new)
        
def abstract():
    raise NotImplementedError('abstract')
        
class CDataModel(object):
    tbl = ''
    m_dataList = []
    dataLoaded = False # 用时导入
    m_fieldNo = {}
    m_fieldNames = []
    m_fieldCNNames = []
    m_fieldDefVals = [] #重构完成之后才能使用
    
    listenerList = []
    
    #True会导致新建对象时无法获取id，实体信息类慎用
    # 关系型数据库可以没有id, fi_key=-1
    autoId = True
    fi_key = 0
    COL_KEY = 0
    
    enableObserver = False
    b_basic_data = True # 基础资料数据，与营业流水数据(禁止拉取缓存)区分
    
    def __init__(self):
        if self.__class__ is CDataModel:
            abstract()
    
        # 有一些子类没有用appendField，所以下一行还有用
        if not self.m_fieldDefVals:
            self.m_fieldVals = [''] * len(self.m_fieldNames)
        else:
            self.m_fieldVals = self.m_fieldDefVals[:]
        
    @classmethod    
    def initClsData(cls, loadData=False):
        '''系统启动时调用，子类需要重写该函数以完成初始化'''
        cls.m_dataList = []
        cls.m_fieldNames = []
        cls.m_fieldCNNames = []
        cls.m_fieldDefVals = []
        cls.listenerList = []
        
        cls.makeField()
        
        if loadData:
            cls.refreshDataList()

    @classmethod
    def makeField(cls):
        '''子类覆盖此函数，定义数据库字段'''
        cls.fi_id = cls.appendField('id', defVal=-1)
    
    @classmethod        
    def appendField(cls, name, cnName='', defVal=''):
        '''TODO - 重构时添加defVal，以便createNew()中使用(cls.m_fieldDefVals)
            TODO - 重构时考虑是否用initFields替代(貌似没有多少必要?)
        '''
        cls.m_fieldNames.append(name)
        cls.m_fieldCNNames.append(cnName)
        cls.m_fieldDefVals.append(defVal)
        return len(cls.m_fieldNames) - 1
    
    #def appendExtraVal(self, val):
    #    '''添加额外的组成数据，以便UI调用时提高效率'''
    #    self.m_fieldVals.append(val)
    #    return len(self.m_fieldVals) - 1
            
    @classmethod        
    def initFields(cls, fieldNames):
        '''fieldNames - [(name, cnName),... ]
           TODO - 重构时添加defVal，以便createNew()中使用
        '''
        index = []
        for name,cnName in fieldNames:
            index.append(cls.appendField(name,cnName))
        return index
        
    @classmethod
    def getObjList(cls):
        '''以列表形式返回数据对象列表，供ctrl遍历数据使用(以免暴露内部实现)'''
        if (not cls.dataLoaded):
            cls.refreshDataList()
        return cls.m_dataList
            
    @classmethod
    def getData(cls, fields, bRecursion=True):
        '''根据字段查找对象实例，以第一个找到的为准
            fields - {'field':val,...}
        '''        
        for obj in cls.getObjList():
            bFind = True
            for field,val in fields.items():
                #print '[getData]%s %s' %(val, obj.m_fieldVals[cls.getFieldNo(field)])
                # if cls.__name__ == "income_statement":
                #     print(val, type(val))
                #     print(obj.m_fieldVals[cls.getFieldNo(field)],type(obj.m_fieldVals[cls.getFieldNo(field)]))
                # if isinstance(val, str) or isinstance(val, unicode):
                #     if val != obj.m_fieldVals[cls.getFieldNo(field)]:
                #         bFind = False
                #         break
                # el
                #if (val.__str__() != obj.m_fieldVals[cls.getFieldNo(field)].__str__()):
                if (kit.myStr(val) != kit.myStr(obj.m_fieldVals[field if isinstance(field, int) else cls.getFieldNo(field)])):
                    #print '[getData]%s %s' %(type(val.__str__()), type(obj.m_fieldVals[cls.getFieldNo(field)].__str__()))
                    #print '[getData]%s %s' %(val, obj.m_fieldVals[cls.getFieldNo(field)])
                    bFind = False
                    break
            if bFind:
                return obj
        
        if (not bRecursion):
            return None

        cls.refreshDataList()
        return cls.getData(fields, False)
            
    @classmethod
    def getInstance(cls, fields):
        print '[getInstance]'
        #如果没有导入数据库，则初始化之
        if (len(cls.m_dataList) == 0):
            cls.refreshDataList()
        obj = cls.getData(fields)
        
        # 160903
        if not obj:                
            cls.refreshDataList()
            obj = cls.getData(fields)
        
        return obj
    
    @classmethod
    def removeInstance(cls, obj=None, fields={}):
        if (obj is None):
            obj = cls.getFirstInstance(fields)
        
        if (obj is not None and cls.m_dataList):
            try:
                cls.m_dataList.remove(obj)
            except:
                print u'removeInstance发生错误.cls:%s len:%d obj:%s' %(cls, len(cls.m_dataList), obj.m_fieldVals[0])
                
    @classmethod
    def getItemCount(cls):
        return len(cls.m_dataList)
    
    @classmethod 
    def getFieldNo(cls, field):
        #print 'getFieldNo %s %s %d' %(cls.__name__, field, len(cls.m_fieldNo))
        #print '%d %d %d' %(len(CDataModel.m_fieldNo), len(CDataProduct.m_fieldNo), len(CDataCategory.m_fieldNo))
        if (len(cls.m_fieldNo) == 0):
            #print 'initing...'
            for i in range(len(cls.m_fieldNames)):                
                cls.m_fieldNo[cls.m_fieldNames[i]] = i
                #print '%s %d' %(cls.m_fieldNames[i], i)
        return cls.m_fieldNo[field]  
    
    @classmethod
    def getFirstFieldVal(cls, inField, outField, byIndex = False):
        '''inField - {'field':xx,}
            outField - 'field'
        '''
        if (byIndex == True):
            #inField - {COL_xx:val,} outField - COL_xx
            return cls.getFirstInstance(inField).getFieldVal(index=outField)
        
##        if (cls is CDataDelivery):
##            return 'None %s' %outField
        
        return cls.getData(inField).getFieldVal(outField)
    
    @classmethod
    def findItemValue(cls, inField, outField): 
        '''inField - {'field':xx,}
            outField - 'field'
        '''
        obj = cls.getFirstInstance(inField)
        if obj is None:
            #print 'Error:None cust %d' %saleOrder.getFieldVal('peerId')
            print 'Error:None Object'
            return None
        else:
            return obj.getValue(outField)
                
    @classmethod
    def getFirstInstance(cls, field):
        '''
            从m_dataList查找，需要确保已装载数据
            inField - {COL_xx:val,}
            return - object
        '''
        if (cls.b_basic_data):
            # 从缓存读取吧
            return cls.getData(field)

        objList = cls.fetch(cls._convertSqlCondition(field))
        if objList:            
            return objList[0] 
        else:
            print u'getFirstInstance发生错误.cls:%s  fields:%s' %(cls, field)
            return None
        
        #print 'getFirstInstance begin'
        index = field.keys()[0]
        for obj in cls.m_dataList:
            #print type(obj.m_fieldVals[index])
            #print obj.m_fieldVals[index]
            if (obj.m_fieldVals[index] == field[index]):
                return obj
            
        log(LOG_ERR, 'unknown obj: %s[%d](%s)%s', (cls, index, type(field[index]), field[index]))
        return None
    
    @classmethod
    def findObj(cls, keyVal):
        '''没有主键的数据表，不可使用该函数'''
        
        if (cls.b_basic_data):
            # 从缓存读取吧
            return cls.getData({cls.fi_key:keyVal})

        objList = cls.fetch(cls._convertSqlCondition({cls.fi_key:keyVal}))
        if objList:            
            return objList[0] 
        else:            
            print u'findObj发生错误.cls:%s  keyVal:%s' %(cls, keyVal)
            print u'sql:%s'%cls._convertSqlCondition({cls.fi_key:keyVal})
            return None
        
        
        for obj in cls.m_dataList:
            if (obj.m_fieldVals[cls.fi_key] == keyVal):
                return obj
   
        # 160903
        
        
        cls.refreshDataList()
        for obj in cls.m_dataList:
            if (obj.m_fieldVals[cls.fi_key] == keyVal):
                return obj
        
        log(LOG_ERR, 'unknown obj: keyVal %s', keyVal)
        return None
        
    @classmethod
    def getObjs(cls, fields):
        '''fields - {COL_xx:val,}'''
        
        #if (len(cls.m_dataList) == 0):
        #    cls.refreshDataList()
            
        list = []
        for obj in cls.getObjList():
            match = True
            for index,val in fields.items():
                if (val != obj.getValue(index)):
                    match = False
                    break
                
            if(match):
                list.append(obj)

        return list
    
    @classmethod
    def getInstanceFromDB(cls):
        pass
        
    def getFieldVal(self, field='',index=-1):
        if(index == -1):
            index = self.getFieldNo(field)
        
        return self.m_fieldVals[index]
    
    def getKeyVal(self):
        return self.m_fieldVals[self.fi_key]
        
    def getValue(self, index):
        return self.m_fieldVals[index]
    
    def setValue(self, index, val):
        self.m_fieldVals[index] = val
        
    def setFieldVal(self, val, field='', index=-1):
        if(index == -1):
            index = self.getFieldNo(field)
        self.m_fieldVals[index] = val
    
    def setMulFieldVal(self, fields):
        '''一次设置多个字段值
        fields = {COL_xx:val, }
        '''
        for (col,val) in fields.items():
            self.m_fieldVals[col] = val
    
    def getFieldCnt(cls):
        return len(cls.m_fieldNames)
            
    @classmethod 
    def getCNName(cls, field='', index=-1):
        if (index == -1):
            return cls.m_fieldCNNames[cls.getFieldNo(field)]
        else:
            return cls.m_fieldCNNames[index]
    
    def populateExtra(self):
        '''渲染不存储在数据表中的对象变量，如saleOrder的(un)paid
            - 在insert/update时，新建数据或更新数据时需要渲染
        '''
        pass
    
    @classmethod    
    def listen(cls):
        '''extra data的获取方式决定应该listen那些类'''
        pass
    
    @classmethod
    def attach(cls, func):
        cls.listenerList.append(func)
    
    def notify(self, op=''):
        '''通知listner发生了变化'''
        if not self.enableObserver:
            return
        
        print ('notify', op, self, self.listenerList)
        for item in self.listenerList:
            item(op, self)
            
    
    @classmethod
    def createNew(cls):
        '''工厂方法 - 主要目的是封装id分配方式，注意子类中如果不是自动int增量，
            需要覆盖 self.assignNewId()'''
        return cls.factory()
    
    @classmethod
    def getNewId(cls):
        '''子类可以覆盖，以定义自己的id产生方式'''
        return cls.newAutoIncrId()
        #return cls.newBillId()
    
    @classmethod
    def factory(cls, newId=True):
        obj = cls()
        if newId and cls.fi_key >= 0:
            obj.setValue(obj.fi_key, obj.getNewId())
            #if cls.autoId:
            #    obj.setValue(obj.fi_key, obj.newAutoIncrId())
            #else:
            #    obj.setValue(obj.fi_key, obj.newBillId())
                
        return obj
            
    def insert(self):
        '''Note:增、删、改都要考虑到其他相关表的信息是否需要修改'''
        sql = 'insert into %s values(' %(self.tbl)
        for i in range(len(self.m_fieldNames)):
            sql += '%s,'
        sql = sql[:-1] + ')'
        
        print (sql, self.m_fieldVals)
        cnt = dbmngr.dbmngr.execute(sql, self.m_fieldVals)

        if (cnt > 0):
            self.__class__.m_dataList.append(self)
            self.populateExtra()
            self.notify('insert')
            
        return cnt
    
    @classmethod
    def quickInsert(cls, fields, newId=True):
        obj = cls.factory(newId)
        obj.setMulFieldVal(fields)
        if obj.insert() > 0:
            return obj
        
        return None
    
    @classmethod
    def tryInsert(cls, fieldDict):
        '''返回id'''
        obj = cls.factory()
        obj.setMulFieldVal(fieldDict)
        cnt = obj.insert()
        if (cnt <= 0):
            obj = cls.getFirstInstance(fieldDict)
        return obj.getValue(cls.COL_ID) if obj else -1
        
    @classmethod
    def deleteRefItem(cls, obj):
        '''子类根据需要选择删除关联数据表中的数据'''
        pass
    
    @classmethod
    def deepDeleteItem(cls, obj):
        return obj.deepDelete()
    
    @classmethod
    def deepDeleteById(cls, keyVal):
        obj = cls.findObj(keyVal)
        if obj:
            return cls.deepDeleteItem(obj)
        
        return 0
            
    def deepDelete(self):
        self.deleteRefItem(self)
        return self.delete()
    
    @classmethod
    def deleteItem(cls, obj):
        obj.delete()
                
    def delete(self):
        if self.fi_key >= 0:
            #sql = 'delete from ' + self.tbl + ' where %s=' %(self.m_fieldNames[self.fi_key])
            #sql += '%s'
            sql = 'delete from '+self.tbl+' where '+self.m_fieldNames[self.fi_key]+'=%s'
            param = (self.m_fieldVals[self.fi_key])
        else:
            sql = 'delete from %s where ' %cls.tbl
            #sql += cls._convertSqlCondition(fields)
            for field in self.m_fieldNames:
                sql += field+'=%s '
                
            param = self.m_fieldVals
        
        cnt = dbmngr.dbmngr.execute(sql, param)
        if cnt > 0:
            self.notify('delete')
            self.removeInstance(self)
            
        return cnt
    
    @classmethod
    def quickDelete(cls, fields):
        '''
            不通过数据对象，而是根据字段数值，快速从数据库中删除，浅删除
            fields - {COL_XX:xx,}  不指定id，因为关系表不一定有id
            return - itemCnt affected
        '''
        cls.removeInstance(fields=fields)
        sql = 'delete from %s where ' %cls.tbl
        sql += cls._convertSqlCondition(fields)
       
        # 临时增加同步机制，重构后删除
        list = cls.getObjs(fields)
        for obj in list:
            obj.notify('delete')
                
        return dbmngr.dbmngr.execute(sql)
               
    @classmethod
    #def delData(cls, fields=None):
    def delData(cls, fields):
        '''浅删除，从数据库中删除数据
            fields - {COL_XX:xx,}  是否应该指定为id？？？
            return - itemCnt affected
        '''        
        if (fields is None):
            sql = 'delete * from %s' %cls.tbl
        else:
            # 141126 - 删除记录时，同时删除缓存实例，以便同步
            cls.removeInstance(fields=fields)
            sql = 'delete from %s where ' %cls.tbl
            sql += cls._convertSqlCondition(fields)
           
            # 临时增加同步机制，重构后删除
            list = cls.getObjs(fields)
            for obj in list:
                obj.notify('delete')
                
        return dbmngr.dbmngr.execute(sql)
        
        # 上面的代码实际上是自身删除，不会删除无关数据，而定义不规范，
        # 有的子类定义为浅删除，有的定义为深删除（saleorderTemp）
        #list = cls.getObjs(fields)
        #for obj in list:
        #    obj.delete()
        #return len(list)
            
    def update(self, cols = []):
        '''设置好数据后，执行该函数以便更新数据库
            cols - [COL_xx,]
        '''
        import datetime
        
        self.populateExtra()
        self.notify('update')
        
        if (len(cols) == 0):
            #update all
            cols = [i for i in range(len(self.m_fieldNames))]
        
        sql = 'update  ' + self.tbl + ' set '    
        for i in cols:
            if (self.m_fieldNames[i] in["lastOrder","last_contact","onhand1","pre_arrears","settle_date"]):
                continue

            # 最初是只有update非None字段，后来有需要将非None设置成None，所以
            # 该段代码也可以改为
            if (self.m_fieldVals[i] is not None):
                sql += '%s=' %(self.m_fieldNames[i])
                #print type(self.m_fieldVals[i])
                if (isinstance(self.m_fieldVals[i], str) | 
                    isinstance(self.m_fieldVals[i], unicode)|
                    isinstance(self.m_fieldVals[i], datetime.date)):
                    sql += "'%s'," %self.m_fieldVals[i]
                else:
                    sql += '%s,' %(str(self.m_fieldVals[i]))
            elif (self.m_fieldDefVals[i] is None):
                sql += self.m_fieldNames[i]+'=NULL,'
            else:
                sql += '%s=%s,' %(self.m_fieldNames[i], str(self.m_fieldDefVals[i]))
                    
        sql = sql[:-1] + ' '
        sql += 'where %s=' %(self.m_fieldNames[self.COL_KEY])
        sql += '%s'
        param = (self.m_fieldVals[self.COL_KEY])
        return dbmngr.dbmngr.execute(sql, param)
    
    @classmethod
    def _convertSqlCondition(cls, fields):
        '''fields - {COL_XX:xx,}
            return - '%s=%s and %s=%s...'
        '''
        sql = ''
        for col,val in fields.items():
            sql += '%s=' %(cls.m_fieldNames[col])
            if (isinstance(val, str) | isinstance(val, unicode)):
                sql += "'%s'" %val
            else:
                sql += '%s' %(str(val))
                
            sql += ' and '
            
        return sql[:-5]
        
    @classmethod
    def alterCols(cls, cols):
        cls.uiCols = cols
        
    def __getitem__(self, i):
        return self.getFieldVal(field=self.uiCols[i])
    
    @classmethod
    def fetchFields(cls, filterSql='', fields=None, orderSql=''):
        
        sql = 'select '
        if fields:
            sql += ','.join(fields)
        else:
            sql += ','.join(cls.m_fieldNames)
            
        sql += ' from ' + cls.tbl
        
        if filterSql:
            sql += ' where ' + filterSql
        
        
        sql += ' ' + orderSql
        cnt = dbmngr.dbmngr.execute(sql)
        if (cnt == 0):
            return []
        result = dbmngr.dbmngr.cursor.fetchall()
        return result
    
    @classmethod
    def fetchBySql(cls, sql):
        '''从数据库中获取数据 select 必须和数据库/数据模型 一一对应
            return - objList fetched from DB
        '''
        objList = []
        print sql
        cnt = dbmngr.dbmngr.execute(sql)
        if (cnt == 0):
            print u'fetch None. sql:%s'%sql
            return []

        result = dbmngr.dbmngr.cursor.fetchall()
        for item in result:
            obj = cls.convertToObj(item)
            objList.append(obj)
        return   objList

    @classmethod
    def fetch(cls, filterSql=''):
        '''从数据库中获取数据
            return - objList fetched from DB
        '''
        objList = []
        sql = u'select * from '+ cls.tbl
        if (filterSql != ''):
            sql += ' where ' + filterSql
        # 140807降序排列
        sql += ' order by %s desc' %(cls.m_fieldNames[cls.COL_KEY])
        print sql
        cnt = dbmngr.dbmngr.execute(sql)
        if (cnt == 0):
            print u'fetch None. sql:%s'%sql
            return []
        result = dbmngr.dbmngr.cursor.fetchall()
        for item in result:
            #obj = cls()
            #for j in range(len(item)):
                #obj.m_fieldVals[j] = item[j]
            obj = cls.convertToObj(item)
            #obj = cls.getFirstInstance({0:item[0], 1:item[1]})
            objList.append(obj)
        return   objList
        
        
    @classmethod
    def fetchData(cls, filterSql='', fields=[]):
        '''从数据库中获取数据
            filterSql - string
            fields - [COL_xx,]
            return - objList fetched from DB
        '''
        if (len(fields) == 0):
            sql = 'select * from '+ cls.tbl
        else:
            sql = 'select '
            for i in range(len(fields)):
                sql += '%s,' %cls.m_fieldNames[fields[i]]
           
            sql = sql[:-1] + ' from ' + cls.tbl
            
        if (filterSql != ''):
            sql += ' where ' + filterSql
            
        cnt = dbmngr.dbmngr.execute(sql)
##        if (cnt == 0):
##            return []
 
        return dbmngr.dbmngr.cursor.fetchall()
    
    @classmethod
    def refreshDataList(cls):
        '''重新从数据库中获取所有数据'''
        
        #if cls in [CDataSaleOrder, CDataPurOrder, CDataSaleDetail, CDataPurDetail] :
        if not cls.b_basic_data:
            print "!!!!!!!!!!!!!!!"
            print u'[error]refreshDataList():%s. 取消操作：远程更新大块数据'%cls.tbl
            print "!!!!!!!!!!!!!!!"
            return
        
        log(TRC, 'refresh %s(avoid repeat)', cls)
        cls.m_dataList = []
        cls.dataLoaded = True
       
        sql = u'select * from '+ cls.tbl# + ' order by ' + cls.m_fieldNames[cls.fi_key] + ' desc'
        cnt = dbmngr.dbmngr.execute(sql)
        ret = dbmngr.dbmngr.cursor.fetchall()

        for item in ret:
            #new = createObj(className)
            #new = cls()
            #for j in range(len(item)):
            #    new.m_fieldVals[j] = item[j]
            new = cls.convertToObj(item)
            cls.m_dataList.append(new)
            
        print ('load ', cls.tbl, cnt, ' items')
        
    @classmethod
    def convertToObj(cls, vals):
        new = cls()
        for i in range(len(vals)):
            new.m_fieldVals[i] = vals[i]
            
        new.populateExtra()
        return new    
    
    @classmethod
    def generateId(cls, date, newId=0):
        
        if not newId:
            sql = 'select count(*) from %s where id like \'%%%s%%\'' %(cls.tbl, date)
            dbmngr.dbmngr.execute(sql)
            
            cnt = dbmngr.dbmngr.cursor.fetchone()[0]
            print ('count() ', cnt)
            
            newId = cnt+1    
        
        idStr = str(newId)
        idStr = cls.suffix[:-len(idStr)] + idStr
        idStr = '%s-%s-%s' %(cls.prefix, date, idStr[-len(cls.suffix):])
        print ('get new bill id ', cls.tbl, idStr)
        
        #if cls.findObj(idStr):
        #    print ('duplicate id:', idStr)
        #    return cls.generateId(date, newId+1)
        sql = 'select count(*) from %s where id="%s"' %(cls.tbl, idStr)
        dbmngr.dbmngr.execute(sql)
        cnt = dbmngr.dbmngr.cursor.fetchone()[0]
        if cnt>0:
            print ('duplicate id:', idStr)
            return cls.generateId(date, newId+1)
        
        return (newId, idStr)  

        
    @classmethod
    def newAutoIncrId(cls):
        '''产生新的商品ID，假设从1开始
            NOTE：从数据库中查找id，因此要避免同时初始化多个同类对象，会导致id冲突
            NOTE:要注意外键id的约束必须要设置，否则删除后新增的id重复会导致问题
        '''
        sql = 'select id from %s order by id asc' %(cls.tbl)
        
        newId = 1
        cnt = dbmngr.dbmngr.execute(sql)
        if (cnt == 0):
            return newId
        
        result = dbmngr.dbmngr.cursor.fetchall()
        for row in result:
            if (newId == row[0]):
                newId = row[0] + 1
            else:
                if(row[0] == 0):
                    log(ERR, 'undefined zero id')
                    break
        
        print '***************new id %d********' %newId
        return newId
    
    # 记录软件启动后已经申请的ID数量，避免同时实例化多个变量出现的ID重复申请
    # Note: 子类中应该重新声明该变量，以免被其他子类修改
    billIdCnt = 0
    maxBillId = ''
    @classmethod
    def newBillId(cls, date):
        # 231030 - 每次都读取新ID，以避免多分机缓存冲突问题
        if True:#(cls.maxBillId == '' or cls.maxBillId[len(cls.prefix)+1:-1-len(cls.suffix)] != date):
            sql = 'select max(id) from %s where id like \'%%%s%%\'' %(cls.tbl, date)
            cnt = dbmngr.dbmngr.execute(sql)
            if (cnt != 0):
                ret = dbmngr.dbmngr.cursor.fetchone()
                #print ('ret ', ret)
                billId = ret[0]
                if (billId is None):
                    newId = 1
                else:
                    newId = int(billId[-len(cls.suffix):]) + 1
            else:
                newId = 1
        else:
            newId = int(cls.maxBillId[-len(cls.suffix):]) + 1
        
        idStr = str(newId)
        idStr = cls.suffix + idStr
        idStr = idStr[-len(cls.suffix):]
        cls.maxBillId = '%s-%s-%s' %(cls.prefix, date, idStr)
        print ('get new bill id ', cls.tbl, cls.maxBillId)
        return cls.maxBillId
    
##        sql = 'select id from %s where id like \'%%%s%%\' order by id desc' %(cls.tbl, date)
##        cnt = dbmngr.dbmngr.execute(sql)
##        if (cnt != 0):
##            ret = dbmngr.dbmngr.cursor.fetchone()
##            #print 'ret %s' %ret
##            newId = int(ret[0][-len(cls.suffix):]) + 1
##        else:
##            newId = 1
##        
##        # 140707 - To avoid confict id for mul object    
##        newId += cls.billIdCnt
##        cls.billIdCnt += 1
##        
##        idStr = str(newId)
##        idStr = cls.suffix + idStr
##        idStr = idStr[-len(cls.suffix):]
##        return '%s-%s-%s' %(cls.prefix, date, idStr)

    @classmethod
    def registerFK(cls, srcTable, fields):
        '''计划重构时用来注册外键，这样删除时，自动去删除外键项目，
            避免到srcTable中添加新的删除代码'''
        pass
    
    @classmethod
    def sync_test(cls):
        
        if CDataVersionTable.isOutofDate(cls.tbl):
            cls.refreshDataList()
          
class CDataProduct(CDataModel):
    tbl = u'product'
    listenerList = []
    m_dataList = []
    m_fieldNo = {}
    m_fieldNames = ['id','name','briefCode','categoryId','brand','spec','onUse',
                    'unit1','ratio1','onhand1','defCost1',
                    'defSale1','lastPur1','minPur1','maxPur1',
                    'unit2','ratio2','onhand2','defCost2',
                    'defSale2','lastPur2','minPur2','maxPur2',
                    'unit3','ratio3','onhand3','defCost3',
                    'defSale3','lastPur3','minPur3','maxPur3',
                    'memo', 'manualNum', 'position','lowest_qty',
                    'notcountable', 'img_prefix',
                    'weight', 'freight_charges', 'min_profit',
                    'lx_profit','whole_profit', 'last_cost'
                    ,'capacity', 'b_overstock', 'b_promot'
                    ,'length','width','area','bulk','package']
    m_fieldCNNames = ['id',u'名称',u'简码',u'类型',u'品牌',u'规格',u'启用',
                    u'单位',u'关系',u'在手',u'预设成本',
                    u'预设售价',u'最近进价',u'最低进价',u'最高进价',
                    'unit2','ratio2','onhand2','defCost2',
                    'defSale2','lastPur2','minPur2','maxPur2',
                    'unit3','ratio3','onhand3','defCost3',
                    'defSale3','lastPur3','minPur3','maxPur3',
                    'memo', 'manualNum', 'position','lowest_qty',
                    'notcountable', 'img_prefix',
                    'weight', 'freight_charges', 'min_profit',
                    'lx_profit','whole_profit', 'last_cost'
                    ,'capacity', 'b_overstock', 'b_promot'
                    ,'length','width','area','bulk','package']
    #autoId = True
    m_fieldDefVals = []
    
    [COL_ID, COL_NAME, COL_BRIEFCODE,COL_CATEGORYID,COL_BRAND,COL_SPEC,COL_ONUSE, 
    COL_UNIT1,COL_RATIO1,COL_ONHAND1,COL_DEFCOST1,COL_DEFSALE1,
    COL_LASTPUR1,COL_MINPUR1,COL_MAXPUR1,
    COL_UNIT2,COL_RATIO2,COL_ONHAND2,COL_DEFCOST2,COL_DEFSALE2,
    COL_LASTPUR2,COL_MINPUR2,COL_MAXPUR2,
    COL_UNIT3,COL_RATIO3,COL_ONHAND3,COL_DEFCOST3,COL_DEFSALE3,
    COL_LASTPUR3,COL_MINPUR3,COL_MAXPUR3, 
    COL_MEMO, COL_MANUALNUM, COL_POSITION, COL_LOWEST_QTY,
    COL_NOTCOUNTABLE, COL_IMGPREFIX,
    COL_WEIGHT, COL_FREIGHT, COL_MINPROFIT,
    COL_LX_PROFIT, COL_WHOLE_PROFIT, COL_LAST_COST
    ,COL_CAPACITY, COL_OVERSTOCK, COL_PROMOT
    ,COL_LENGTH,COL_WIDTH,COL_AREA,COL_BULK,COL_PACKAGE] = [i for i in range(51)]
    
    UNIT_CNT = 3
    UNIT_FIELD_CNT = 8
    
    def __init__(self):
        CDataModel.__init__(self)
        self.m_fieldDefVals = [-1,'','',1,'','',1,
                        '',None,None,None,None,None,None,None,
                        '',None,None,None,None,None,None,None,
                        '',None,None,None,None,None,None,None,
                        '',None,'',0,0,'',
                        0,0,0,0,0,0,0,0,0
                        ,0,0,0,0,0]
                        
        self.m_fieldVals = [-1,'','',1,'','',1,
                        '',None,None,None,None,None,None,None,
                        '',None,None,None,None,None,None,None,
                        '',None,None,None,None,None,None,None,
                        '',None,'',0,0,'',
                        0,0,0,0,0,0,0,0,0
                        ,0,0,0,0,0]

##    @classmethod
##    def createNew(cls):
##        '''工厂方法 - 主要目的是封装id分配方式'''
##        obj = cls()
##        
##        obj.m_fieldVals = [-1,'','',1,'','',1,
##                        '',None,None,None,None,None,None,None,
##                        '',None,None,None,None,None,None,None,
##                        '',None,None,None,None,None,None,None,
##                        '']
##        
##        obj.setValue(cls.COL_ID, cls.newId())
##        return obj
    
    def validUnit(self, iUnit):
        return self.getValue(iUnit) and self.getValue(iUnit) != ''
        
    def getUnratioUnit(self, unit):
        if not self.validUnit(self.COL_UNIT2):
            return []
        
        ratioUnits = []
        unratioUnits = []
        for i in range(3):
            iUnit = self.COL_UNIT1 + i * self.UNIT_FIELD_CNT
            if self.validUnit(iUnit):
                iRatio = iUnit + 1
                if self.getValue(iRatio) is None or self.getValue(iRatio) == '':
                    unratioUnits.append(self.getValue(iUnit))
                else:
                    ratioUnits.append(self.getValue(iUnit))
            else:
                break
        
        print(ratioUnits, unratioUnits)
        
        if unit not in ratioUnits:
            unratioUnits.remove(unit)
            if ratioUnits:
                unratioUnits.append(ratioUnits[0])
                
        return unratioUnits
                
    def getUnitDict(self):
        dict = {}
        dict[self.m_fieldVals[self.COL_UNIT1]] = self.m_fieldVals[self.COL_UNIT1+1:self.COL_MAXPUR1+1]
        if self.m_fieldVals[self.COL_UNIT2] and self.m_fieldVals[self.COL_UNIT2] != '':
            dict[self.m_fieldVals[self.COL_UNIT2]] = self.m_fieldVals[self.COL_UNIT2+1:self.COL_MAXPUR2+1]
        
        if self.m_fieldVals[self.COL_UNIT3] and self.m_fieldVals[self.COL_UNIT3] != '':
            dict[self.m_fieldVals[self.COL_UNIT3]] = self.m_fieldVals[self.COL_UNIT3+1:self.COL_MAXPUR3+1]
            
        return dict
    
    def validRatio(self, ratio):
        return ratio is not None and ratio != ''
        
    def syncUnitVal(self, baseLine, num):
        '''根据某一行的值，重新设置其他存在数量关系行的数据'''
        iRatio = self.COL_RATIO1 + baseLine * self.UNIT_FIELD_CNT
        b_ratio = self.getValue(iRatio)
        if(self.validRatio(b_ratio)):
            #更新其他
            print u'TODO:产品数量关系更新'
            for i in range(self.UNIT_CNT):
                if i == baseLine:
                    continue
                iRatio = self.COL_RATIO1 + i * self.UNIT_FIELD_CNT
                i_ratio = self.getValue(iRatio)
                if (self.validRatio(i_ratio)):
                    iOnhand = self.COL_ONHAND1 + i * self.UNIT_FIELD_CNT
                    i_num = num * i_ratio / b_ratio
                    self.setValue(iOnhand, i_num + self.getValue(iOnhand))
    
    def setUnitData(self, unitVals):
        for i in range(len(unitVals)):
            self.m_fieldVals[self.COL_UNIT1+i] = unitVals[i]
            
        if (i != 23):
            log(ERR, 'Incorrect index %d', i)
    
    def refreshOnhand(self):
    
        proId = self.getValue(self.COL_ID)
        
        # 多仓库改用触发器更新 onhand1
        #sql = "update product,product_extra set onhand1=quantity where product_id=id"
        #dbmngr.dbmngr.execute(sql)
        
        #sql = 'select quantity from product_extra where product_id=%d' %(proId)
        sql = '''select onhand1,wh_position
            from product left join product_extra on product.id=product_id and warehouse_id=%d
            where id=%d''' %(CDataWarehouse.wh_main_id, proId)
        cnt = dbmngr.dbmngr.execute(sql)
        if (cnt != 0):
            onhand1,position = dbmngr.dbmngr.cursor.fetchone()
            self.setValue(self.COL_ONHAND1, onhand1)
            self.setValue(self.COL_POSITION, position)

    @staticmethod 
    def refreshCacheOnhand(id_list):
        ''' 商品库存变动后，onhand1会由trigger修改，电脑缓存要更新一下 '''

        sql = '''select product.id,onhand1,wh_position
            from product left join product_extra on product.id=product_id and warehouse_id=%d
            where id in (%s)''' %(CDataWarehouse.wh_main_id, ",".join(str(i) for i in id_list))
        cnt = dbmngr.dbmngr.execute(sql)
        if (cnt != 0):
            result = dbmngr.dbmngr.cursor.fetchall()
            for id,onhand1,position in result:
                proObj = CDataProduct.getData({'id':id})
                if proObj is not None:
                    proObj.setValue(proObj.COL_ONHAND1, onhand1)
                    proObj.setValue(proObj.COL_POSITION, position)

        
    def updateQuantity(self, wh_id, position, qty):
        ''' 直接更新库存数量，用于盘点修改 
        '''
        pass
        # proId = self.getValue(self.COL_ID)
        # sql = "insert into product_extra (product_id, quantity) values ("
        # sql += str(proId) + "," + str(qty) +")"
        # sql += "ON DUPLICATE KEY update quantity = (" + str(qty) + ")"
        # dbmngr.dbmngr.execute(sql)
    
        # self.refreshOnhand()    
        
    
    @classmethod    
    def updateOnhand(cls, proId, unit, num):
        ''' 直接更新库存数量，用于盘点修改 
        '''
        return 
        
        
    @classmethod
    def modifyOnhand(cls, proId, unit, num):
        '''销售时更新在手数量'''
        return # 作废该函数，使用触发器

        # 220629 - 改为使用新表存储库存，更新后自动更新缓存库存以便正确显示
        # 只处理单位1库存
        
        sql = "insert into product_extra (product_id, quantity) values ("
        sql += str(proId) + "," + str(num) +")"
        sql += "ON DUPLICATE KEY update quantity += (" + str(num) + ")"
        dbmngr.dbmngr.execute(sql)        
        
        obj = cls.getFirstInstance({cls.COL_ID:proId})
        obj.refreshOnhand()
        
        # return
        # obj = cls.getFirstInstance({cls.COL_ID:proId})
        # for i in range(cls.UNIT_CNT):
            # iUnit = cls.COL_UNIT1 + i * cls.UNIT_FIELD_CNT
            # if(unit == obj.getValue(iUnit)):
                # iOnhand = cls.COL_ONHAND1 + i * cls.UNIT_FIELD_CNT
                # obj.setValue(iOnhand, num + obj.getValue(iOnhand))
                
                # obj.syncUnitVal(i, num)
            
                # obj.update()
                # return
    
    def calcAvgCost(self, unit, onhand):
        pass
        
    @classmethod
    def updatePurchase(cls, proId, unit, price, num):
        '''采购订单更新采购价格、数量
        
            220630 - 这个函数找不到调用
        '''

        obj = cls.getFirstInstance({cls.COL_ID:proId})
        
        for i in range(cls.UNIT_CNT):
            iUnit = cls.COL_UNIT1 + i * cls.UNIT_FIELD_CNT
            if(unit == obj.getValue(iUnit)):
                iRatio = cls.COL_RATIO1 + i * cls.UNIT_FIELD_CNT
                iOnhand = cls.COL_ONHAND1 + i * cls.UNIT_FIELD_CNT
                iCost = cls.COL_DEFCOST1 + i * cls.UNIT_FIELD_CNT
                iSale = cls.COL_DEFSALE1 + i * cls.UNIT_FIELD_CNT
                iLastPrice = cls.COL_LASTPUR1 + i * cls.UNIT_FIELD_CNT
                iMinPur = cls.COL_MINPUR1 + i * cls.UNIT_FIELD_CNT
                iMaxPur = cls.COL_MAXPUR1 + i * cls.UNIT_FIELD_CNT
                
                #obj.setValue(iOnhand, num + obj.getValue(iOnhand))
                #if(obj.getValue(iCost) == 0.0):
                   # 浮点比较？？
                   #obj. setValue(iCost, price*1.05) # 去掉这里，会导致零价格商品自动设置成本价
                
                # 文字型商品
                #if(obj.getValue(iSale) == 0.0):
                   # 浮点比较？？
                   #obj. setValue(iSale, price*1.2)
                
                # 文字型商品，不更新价格
                if (obj.getValue(obj.COL_CATEGORYID) != 33):
                    if num > 0:
                        obj.setValue(iLastPrice, price)
                    
                    if(price < obj.getValue(iMinPur) or obj.getValue(iMinPur) == 0.0):
                        obj.setValue(iMinPur, price)
            
                    if(price > obj.getValue(iMaxPur) or obj.getValue(iMaxPur) == 0.0):
                        obj.setValue(iMaxPur, price)
                        
                obj.syncUnitVal(i, num)
                
        obj.update()
               
    @classmethod
    def newId(cls):
        '''产生新的商品ID，假设数据库编号从1开始，从0开始会出错'''
        sql = 'select id from %s order by id asc' %(cls.tbl)
        
        newId = 1
        cnt = dbmngr.dbmngr.execute(sql)
        if (cnt == 0):
            return newId
        
        result = dbmngr.dbmngr.cursor.fetchall()
        for row in result:
            #print '%d %d' %(newId, row[0])
            if (newId == row[0]):
                newId += 1
            else:
                break
                        
        return newId
    
    def isDeletable(self):
        return False
    
    def isOnuse(self):
        if (self.getValue(self.COL_ONUSE) != 1):
            return False
        return True
    
    def enable(self, enable=True):
        if (enable):
            self.setValue(self.COL_ONUSE, 1)
        else:
            self.setValue(self.COL_ONUSE, 0)
        self.update()
            
    def attachLabel(self, labelId):
        obj = CDataProLabel.factory()
        fields = {CDataProLabel.COL_PROID:self.m_fieldVals[self.COL_ID],
                    CDataProLabel.COL_LABELID:labelId}
        obj.setMulFieldVal(fields)
        obj.insert()
    
    def getPricePur(self, unit=u''):
        if not unit:
            return self.getValue(self.COL_LASTPUR1)
        
        if unit == self.getValue(self.COL_UNIT1):
            return self.getValue(self.COL_LASTPUR1)
        elif unit == self.getValue(self.COL_UNIT2):
            return self.getValue(self.COL_LASTPUR2)
        elif unit == self.getValue(self.COL_UNIT3):
            return self.getValue(self.COL_LASTPUR3)
        else:
            assert u'未知单位'==unit
            
        return 0.0
    
    @classmethod    
    def getManualNum(cls, proId):
        proObj = cls.findObj(proId)
        return proObj.getValue(proObj.COL_MANUALNUM)
        
    @staticmethod    
    def get_purchase_report(filter_sql):
        sql = '''SELECT id, product.position, `name`, spec, unit1,defSale1,defCost1,lowest_qty,quantity
            , s30_sale, s90_sale , s180_sale, s360_sale,ROUND(last_cost*quantity,0) as total,lastPur1,onhand1,product.memo
            FROM product
            LEFT JOIN product_extra ON id = product_id and warehouse_id = 1
            LEFT JOIN (select proId,sum(num) as s30_sale
              FROM saledetails 
                LEFT JOIN saleorder ON saledetails.billId=saleorder.id 
              WHERE DATEDIFF(NOW(), billDate) < 30 GROUP BY proId) s30 ON id = s30.proId
            LEFT JOIN (select proId,sum(num) as s90_sale
              FROM saledetails 
                LEFT JOIN saleorder ON saledetails.billId=saleorder.id 
              WHERE DATEDIFF(NOW(), billDate) < 90 GROUP BY proId) s90 ON id = s90.proId
            LEFT JOIN (select proId,sum(num) as s180_sale
              FROM saledetails 
                LEFT JOIN saleorder ON saledetails.billId=saleorder.id 
              WHERE DATEDIFF(NOW(), billDate) < 180 GROUP BY proId) s180 ON id = s180.proId
            LEFT JOIN (select proId,sum(num) as s360_sale
              FROM saledetails 
                LEFT JOIN saleorder ON saledetails.billId=saleorder.id 
              WHERE DATEDIFF(NOW(), billDate) < 360 GROUP BY proId) s360 ON id = s360.proId
            WHERE %s GROUP BY product_id''' %(filter_sql) 

        cnt = dbmngr.dbmngr.execute(sql)
        print sql
        print 'cnt %d' %cnt
        if (cnt != 0): 
            return dbmngr.dbmngr.cursor.fetchall()
        else:
            return []
    
    @staticmethod    
    def getProfitPro(filter_sql):
        '''获取商城商品列表'''
        sql = '''SELECT product.id,product.`name`,product.spec
                    ,SUM(num)
                    ,SUM(saledetails.total) as total_sales
                    ,ROUND(avg(price),2)
                    ,ROUND(last_cost,2)
                    ,ROUND(avg(price-last_cost),2)
                    ,ROUND(SUM(saledetails.total -last_cost*num),2) as total_profit	
                    ,ROUND(avg((price-last_cost)/price),2)
                    ,onhand1
                    ,lowest_qty 
                FROM saledetails 
                    LEFT JOIN saleorder ON saledetails.billId=saleorder.id 
                    LEFT JOIN product ON proId=product.id
                    LEFT JOIN customer ON saledetails.peerId=customer.id
                WHERE %s AND num > 0 AND last_cost > 0 AND type!=15 AND categoryId NOT IN (33,34,69)
                GROUP BY proId
                ORDER BY total_profit''' %(filter_sql)

        cnt = dbmngr.dbmngr.execute(sql)
        print sql
        print 'cnt %d' %cnt
        if (cnt != 0): 
            return dbmngr.dbmngr.cursor.fetchall()
        else:
            return []

    @staticmethod    
    def getProfitProOnMall(filter_sql):
        ''' 获取商城销量商品的利润 '''
        sql = '''SELECT product.id
                    ,o.billDate
                    ,proName
                    ,product.spec
                    ,num
                    ,d.total as total_sales
                    ,ROUND(if(p.routeId=40, (d.total)*%.3f, (d.total)*%.3f),2)
                    ,ROUND(d.total -last_cost*num,2) - ROUND(if(p.routeId=40, (d.total)*%.3f, (d.total)*%.3f),2)
                    ,ROUND(price,2)
                    ,ROUND(if(p.routeId=40, price*%.3f, price*%.3f),2)
                    ,ROUND(last_cost,2)
                    ,ROUND(price-last_cost,2)-ROUND(if(p.routeId=40, price*%.3f, price*%.3f),2)
                    ,ROUND((price-last_cost-if(p.routeId=40, price*%.3f, price*%.3f))/price,2)
                    ,onhand1
                    ,lowest_qty
                    ,r.`name`
                    ,customer.`name`
                FROM saledetails d
                    LEFT JOIN saleorder o ON o.id=d.billId
                    LEFT JOIN delivery_plan p ON o.deliveryPlan=p.Id
                    LEFT JOIN delivery_route r ON r.Id=p.routeId
                    LEFT JOIN product ON d.proId=product.id
                    LEFT JOIN customer ON o.peerId=customer.id
                WHERE %s and price > 0 and abs(num) > 0 and categoryId NOT IN (33,34,69)
                ORDER BY proName DESC''' %(CDataMallProfit.Tong_Kickback,CDataMallProfit.Yi_Kickback
                , CDataMallProfit.Tong_Kickback,CDataMallProfit.Yi_Kickback
                , CDataMallProfit.Tong_Kickback,CDataMallProfit.Yi_Kickback
                , CDataMallProfit.Tong_Kickback,CDataMallProfit.Yi_Kickback
                , CDataMallProfit.Tong_Kickback,CDataMallProfit.Yi_Kickback
                , filter_sql)

        cnt = dbmngr.dbmngr.execute(sql)
        print sql
        print 'cnt %d' %cnt
        if (cnt != 0): 
            return dbmngr.dbmngr.cursor.fetchall()
        else:
            return []


    @staticmethod    
    def get_region_sales(filter_sql):
        sql = '''SELECT product.id, onhand1, product.name, spec, unit1, sum(num) AS total, quantity, defSale1, defCost1
            FROM product
            LEFT JOIN product_extra ON product_id = product.id and warehouse_id = 1
            LEFT JOIN saledetails ON proId = product.id
            LEFT JOIN customer ON peerId = customer.id
            WHERE %s
            GROUP BY proId 
            ORDER BY total DESC''' %(filter_sql) 

        cnt = dbmngr.dbmngr.execute(sql)
        # print 'cnt %d' %cnt
        if (cnt != 0): 
            return dbmngr.dbmngr.cursor.fetchall()
        else:
            return []

    @staticmethod    
    def get_purchase_plan(type, categoryIds=None):
        sql = "select p.id,proId,name,spec,unit,qty,price,price*qty,p.memo,product.position,product.onhand1 "\
            "from purchase_plan p "\
            "left join product on proId=product.id "\
            "where status=%d %s" %(type, "and categoryId in (%s)"%(",".join(str(e) for e in categoryIds)) if (categoryIds is not None and len(categoryIds)>0) else "")
            
        cnt = dbmngr.dbmngr.execute(sql)
        print sql
        print 'cnt %d' %cnt
        if (cnt != 0): 
            return dbmngr.dbmngr.cursor.fetchall()
        else:
            return []    
    
    @staticmethod
    def add_purchase_plan(proId,unit,qty,price):
        sql = "insert into purchase_plan (proId,unit,qty,price) values "\
            "(%d,'%s',%g,%g)" %(proId,unit,qty,price)
        dbmngr.dbmngr.execute(sql)    
    
    @staticmethod
    def delete_purchase_plan(id):
        sql = "delete from purchase_plan where id=%d" %id
        dbmngr.dbmngr.execute(sql)

    @staticmethod
    def delete_purchase_plan_by_type(status):
        sql = "delete from purchase_plan where status=%d" %status
        dbmngr.dbmngr.execute(sql)         
        
    @staticmethod    
    def update_purchase_plan(id, status=None, qty=None, price=None, memo=None):
        val = []
        if status is not None:
            val.append("status=%d"%status)
        if qty is not None:
            val.append("qty=%.2f"%qty)
        if price is not None:
            val.append("price=%.2f"%price)
        if memo is not None:
            val.append("memo='%s'"%memo)
        sql = "update purchase_plan set %s "\
            "where id=%d" %(",".join(val),id)
            
        dbmngr.dbmngr.execute(sql)
    
    @staticmethod 
    def change_sku(pro_id, sku_ids):
        sql = "delete from product_sku where pro_id=%d "%(pro_id)            
        dbmngr.dbmngr.execute(sql)
        
        sql = "insert into product_sku (pro_id,sku_id) values "
        for sku_id in sku_ids:
            sql += "(%d,%d)," %(pro_id, int(sku_id)) 
        
        dbmngr.dbmngr.execute(sql.strip(','))
        
    @staticmethod 
    def get_sku(pro_id):
        sql = "select group_id,sku_id from product_sku "\
            " LEFT JOIN sku_item ON sku_id=sku_item.id "\
            " WHERE pro_id=%d " %(pro_id)  

        cnt = dbmngr.dbmngr.execute(sql)
        print sql
        print 'cnt %d' %cnt
        if (cnt != 0): 
            return dbmngr.dbmngr.cursor.fetchall()
        else:
            return []
    
    @staticmethod 
    def get_sku_str(pro_id):
        sql = "select group_concat(sku_name separator ' ') from product_sku "\
            " LEFT JOIN sku_item ON sku_id=sku_item.id "\
            " WHERE pro_id=%d "\
            " ORDER BY seq " %(pro_id)  

        cnt = dbmngr.dbmngr.execute(sql)
        print sql
        print 'cnt %d' %cnt
        if (cnt != 0): 
            return dbmngr.dbmngr.cursor.fetchall()[0][0]
        else:
            return ""    
    
    def get_category_name(self):
        category = CDataCategory.getData({'id':self.getValue(self.COL_CATEGORYID)})
        return category.getValue(category.COL_NAME)
    
    
    @staticmethod 
    def get_pro_list(filterSql):
        sql = "select product.id,product.name"\
            ",'','','','','',product.name,''"\
            ",category.name,product.spec,weight,if(onUse=1,'O','X') "\
            ",unit1"\
            ",t1.quantity"\
            ",if (last_cost>20, ceil(last_cost+whole_profit), round(last_cost+whole_profit,2))"\
            ",if (last_cost>20, ceil(last_cost+lx_profit), round(last_cost+lx_profit,2))"\
            ",if (last_cost>20, ceil(last_cost+min_profit), round(last_cost+min_profit,2))"\
            ",last_cost"\
            ",lastPur1,product.memo"\
            ",CONCAT(ifnull(t1.wh_position,''),if(t1.wh_position is null or t1.wh_position='' or t1.wh_pos2='', '', '/'),ifnull(t1.wh_pos2,''))"\
            ",IFNULL(t1.quantity,0)+IFNULL(t2.quantity,0)+IFNULL(t3.quantity,0)"\
            ",CONCAT(ifnull(t2.wh_position,''),if(t2.wh_position is null or t2.wh_position='' or t2.wh_pos2='', '', '/'),ifnull(t2.wh_pos2,''))"\
            ",t2.quantity"\
            ",CONCAT(ifnull(t3.wh_position,''),if(t3.wh_position is null or t3.wh_position='' or t3.wh_pos2='', '', '/'),ifnull(t3.wh_pos2,''))"\
            ",t3.quantity"\
            ",lowest_qty"\
            ",defSale1"\
            ",round(last_cost/0.8,1)"\
            ",defSale1-round(last_cost/0.8,1)"\
            ",concat('A',product.id)"\
            ",if (last_cost>20, ceil((last_cost+whole_profit)*1.5), round((last_cost+whole_profit)*1.5,2))"\
            ",if (last_cost>20, ceil((last_cost+whole_profit)*1.1), round((last_cost+whole_profit)*1.1,2))"\
            ",if (last_cost>20, ceil((last_cost+whole_profit)*1.05), round((last_cost+whole_profit)*1.05,2))"\
            ",if (last_cost>20, ceil(last_cost+whole_profit), round(last_cost+whole_profit,2))"\
            ",if (last_cost>20, ceil((last_cost+min_profit)*1.05), round((last_cost+min_profit*1.05),2))"\
            ",lastPur1"\
            ",last_cost"\
            ",CONCAT(ifnull(t1.wh_position,''),if(t1.wh_position is null or t1.wh_position='' or t1.wh_pos2='', '', '/'),ifnull(t1.wh_pos2,''))"\
            ",product.spec"\
            ",if (t1.quantity>0, t1.quantity, '')"\
            ",if (t1.quantity>0, last_cost, '')"\
            ",if (t2.quantity>0, t2.quantity, '')"\
            ",if (t2.quantity>0, last_cost, '')"\
            " from product "\
            " left join category on categoryId=category.id "\
            " LEFT JOIN product_extra t1 ON t1.product_id=product.id AND t1.warehouse_id=1"\
            " LEFT JOIN product_extra t2 ON t2.product_id=product.id AND t2.warehouse_id=2"\
            " LEFT JOIN product_extra t3 ON t3.product_id=product.id AND t3.warehouse_id=11"

        if (filterSql != ''):
            sql += ' where ' + filterSql

        sql += ' order by product.name '

        cnt = dbmngr.dbmngr.execute(sql)
        print sql
        print 'cnt %d' %cnt
        if (cnt != 0):
            return dbmngr.dbmngr.cursor.fetchall()
        else:
            return []

    @staticmethod 
    def CheckNegative(id_list):
        '''检查是否库存不足'''
        print (id_list)
        sql = """select concat(IFNULL(e.wh_position,'Y001'),',\t',name,',\t',spec,',\t',onhand1) 
            from product left join product_extra e on product.id=e.product_id and e.warehouse_id=%d
            where onhand1<0 and id in (%s)
            """ %(CDataWarehouse.wh_main_id, ",".join(str(i) for i in id_list))
        cnt = dbmngr.dbmngr.execute(sql)
        if (cnt != 0):
            return dbmngr.dbmngr.cursor.fetchall()
        else:
            return []
    
    
    @staticmethod 
    def CheckTransfer(id_list, wh_out):
        "检查出库仓库是否库存不足，提示调拨"
        print (id_list)
        sql = """select concat(IFNULL(e.wh_position,'Y001'),',\t',name,',\t',spec,',\t',quantity) 
            from product left join product_extra e on product.id=e.product_id and e.warehouse_id=%d
            where onhand1>=0 and quantity<0 and id in (%s)
            """ %(wh_out, ",".join(str(i) for i in id_list))
        cnt = dbmngr.dbmngr.execute(sql)
        if (cnt != 0):
            return dbmngr.dbmngr.cursor.fetchall()
        else:
            return []

    def GetLowestPrice(self):
        '''获得底价'''

        import math
        if (self.getValue(self.COL_MINPROFIT) > 0):
            price = self.getValue(self.COL_LAST_COST) + self.getValue(self.COL_MINPROFIT)
            return math.ceil(price) if price>10 else price
        # else:
        #    return math.ceil(self.getValue(self.COL_LAST_COST) * 1.1)
        return 0

    @staticmethod
    def ResetBatchPrice():
        '''重新设置批发价'''

        # 按最近半年卖货次数最多的价格，更新预设售价 -- 最近半年销售3次以上才会更新
        sql ='''UPDATE product INNER JOIN
            (
            SELECT t1.proId, t1.price, t1.cnt  
            FROM (  
                SELECT proId,price,count(*) as cnt
                    FROM saledetails
                    WHERE total>0 and billId > CONCAT("XS-", DATE_FORMAT(DATE_SUB(CURDATE(), INTERVAL 6 MONTH),'%Y%m%d'))  
                    GROUP BY proId,price
                    HAVING cnt >= 3 
            ) AS t1  
            INNER JOIN (  
                SELECT proId, MAX(cnt) as max_cnt  
                FROM (  
                    SELECT proId,price,count(*) as cnt
                            FROM saledetails
                            WHERE total>0 and billId > CONCAT("XS-", DATE_FORMAT(DATE_SUB(CURDATE(), INTERVAL 6 MONTH),'%Y%m%d'))  
                            GROUP BY proId,price
                            HAVING cnt >= 3 
                ) AS subquery  
                GROUP BY proId  
            ) AS t2  
            ON t1.proId = t2.proId  AND t1.cnt = t2.max_cnt
            ) T3 ON product.id=T3.proId
            SET product.defSale1=T3.price WHERE categoryId NOT IN (33,34,69)'''
        cnt = dbmngr.dbmngr.execute(sql)
        print sql
        print 'cnt %d' %cnt

    @staticmethod
    def GetStockInfo(proId):
        '''获取商品的库存信息'''

        sql = """SELECT w.id, w.briefCode, w.name, e.quantity, e.wh_position, e.wh_pos_memo
                , e.wh_pos2, e.wh_pos2_memo, e.wh_memo FROM warehouse w
            LEFT JOIN product_extra e ON w.id=e.warehouse_id AND product_id=%d""" %(proId)
        cnt = dbmngr.dbmngr.execute(sql)
        if (cnt != 0):
            return dbmngr.dbmngr.cursor.fetchall()
        else:
            return []

    @staticmethod
    def UpdateStockInfo(proId, whList):
        ''' 多库存的数据更新到数据库 '''
        sql = "delete from product_extra where product_id=%d" %(proId)
        dbmngr.dbmngr.execute(sql)

        b_insert = False
        sql = "insert into product_extra (product_id,quantity,warehouse_id,wh_position,wh_pos_memo,wh_pos2,wh_pos2_memo,wh_memo) values "
        for row in whList:
            wh_id,wh_code,wh_name,qty,wh_pos1,wh_pos1_memo,wh_pos2,wh_pos2_memo,memo = row
            if (qty !=""):
                sql += "(%s,%s,%s,'%s','%s','%s','%s','%s')," %(proId, qty, wh_id, wh_pos1,wh_pos1_memo,wh_pos2,wh_pos2_memo, memo)
                b_insert = True

        if b_insert:
            dbmngr.dbmngr.execute(sql[:-1])

    @staticmethod
    def SetStockInfo(proId, jinbo_qty,jinbo_pos,wuming_qty,wuming_pos,cheguansuo_qty,cheguansuo_pos):
        
        if jinbo_qty is not None or jinbo_pos is not None:
            sql = """insert into product_extra (product_id, warehouse_id, quantity, wh_position) 
                values ('%d',1,%g,'%s') ON DUPLICATE KEY update quantity=%g,wh_position='%s'""" %(proId
                ,jinbo_qty or 0
                ,jinbo_pos or ""
                ,jinbo_qty or 0
                ,jinbo_pos or "")
            dbmngr.dbmngr.execute(sql)

        if wuming_qty is not None or wuming_pos is not None:
            sql = """insert into product_extra (product_id, warehouse_id, quantity, wh_position) 
                values ('%d',2,%g,'%s') ON DUPLICATE KEY update quantity=%g,wh_position='%s'""" %(proId
                ,wuming_qty or 0
                ,wuming_pos or ""
                ,wuming_qty or 0
                ,wuming_pos or "")
            dbmngr.dbmngr.execute(sql)

        if cheguansuo_qty is not None or cheguansuo_pos is not None:
            sql = """insert into product_extra (product_id, warehouse_id, quantity, wh_position) 
                values ('%d',11,%g,'%s') ON DUPLICATE KEY update quantity=%g,wh_position='%s'""" %(proId
                ,cheguansuo_qty or 0
                ,cheguansuo_pos or ""
                ,cheguansuo_qty or 0
                ,cheguansuo_pos or "")
            dbmngr.dbmngr.execute(sql)


    @staticmethod
    def GetStockQty(proId, wh=-1):
        '''读取库存'''
        sql = "select t1.quantity"\
            ",CONCAT(ifnull(t1.wh_position,''),if(t1.wh_position is null or t1.wh_position='' or t1.wh_pos2 is null or t1.wh_pos2='', '', '/'),ifnull(t1.wh_pos2,''))"\
            ",t2.quantity"\
            ",CONCAT(ifnull(t2.wh_position,''),if(t2.wh_position is null or t2.wh_position='' or t2.wh_pos2 is null or t2.wh_pos2='', '', '/'),ifnull(t2.wh_pos2,''))"\
            ",t3.quantity"\
            ",CONCAT(ifnull(t3.wh_position,''),if(t3.wh_position is null or t3.wh_position='' or t3.wh_pos2 is null or t3.wh_pos2='', '', '/'),ifnull(t3.wh_pos2,''))"\
            " from product "\
            " LEFT JOIN product_extra t1 ON t1.product_id=product.id AND t1.warehouse_id=1"\
            " LEFT JOIN product_extra t2 ON t2.product_id=product.id AND t2.warehouse_id=2"\
            " LEFT JOIN product_extra t3 ON t3.product_id=product.id AND t3.warehouse_id=11"\
            " where id=%d" %(proId)

        #print sql

        cnt = dbmngr.dbmngr.execute(sql)
        if (cnt != 0):
            return dbmngr.dbmngr.cursor.fetchall()[0]
        else:
            return [0,'',0,'',0,'']

class CDataCategory(CDataModel):
    tbl = u'category'
    m_dataList = []
    m_fieldNo = {}
    m_fieldNames = ['id' ,'name', 'parentId']
    m_fieldCNNames = []
    m_fieldDefVals = [-1,'','']
    autoId = True
    
    [COL_ID, COL_NAME, COL_PARENT] = [i for i in range(3)]
    
    @classmethod
    def getName(cls, id):
        return cls.getFirstFieldVal({'id':id}, 'name')

    @classmethod
    def getDefCategoryId(cls):
        return 1
    
    @classmethod
    def getDefaultObj(cls):
        id = cls.getDefCategoryId()
        
        return cls.getFirstInstance({cls.COL_ID:id})
        
    @classmethod
    def getChildId(cls, pid):
        child = [pid]
        
        for id in child:
            filter = 'parentId=%d' %id
            objList = CDataCategory.fetch(filter)
            for obj in objList:
                child.append(obj.getFieldVal(index=CDataCategory.COL_ID))
        
        log(TRC, 'child of %d is %s', (pid, child))
        return child
    
##    def __init__(self):    
##        CDataModel.__init__(self)
##        self.m_fieldVals = [''] * 3

    @staticmethod
    def get_sku(categoryId):
        
        sql = "SELECT sku_group.id "\
            ", sku_group.group_name"\
            ", GROUP_CONCAT(sku_item.id, '::' , sku_name ORDER BY sku_item.seq ASC separator ';;') "\
            " FROM sku_item "\
            " LEFT JOIN sku_group ON group_id=sku_group.id "\
            " WHERE category_id=%d"\
            " GROUP BY sku_group.id "\
            " ORDER BY sku_group.seq ASC" %(categoryId)
            
        cnt = dbmngr.dbmngr.execute(sql)
        print sql
        print 'cnt %d' %cnt
        if (cnt != 0): 
            return dbmngr.dbmngr.cursor.fetchall()
        else:
            return [] 
        
    
class CDataKeyword(CDataModel):
    tbl = u'keyword'
    m_dataList = []
    m_fieldNo = {}
    m_fieldNames = ['id', 'name']
    m_fieldCNNames = []
    m_fieldDefVals = [-1, '']
    #autoId = True
    
    [COL_ID, COL_NAME] = [i for i in range(2)]
    
    @classmethod
    def add(cls, name):
        obj = cls.factory()
        obj.setValue(cls.COL_NAME, name)
        cnt = obj.insert()
        if (cnt <= 0):
            obj = cls.find(name)
        return obj.getValue(cls.COL_ID) if obj else -1
    
    @classmethod
    def find(cls, name):
        return cls.getFirstInstance({cls.COL_NAME:name})
    
    @classmethod
    def parseStr(cls, objList):
        str = ''
        for obj in objList:
            str += '%s ' %obj.getFieldVal(index=CDataKeyword.COL_NAME)
        return str
        
##    def __init__(self):    
##        CDataModel.__init__(self)
##        self.m_fieldVals = [''] * 2

class CDataProLabel(CDataModel):
    '''关系型数据表，描述商品与关键字对应关系'''
    tbl = u'proLabeling'
    m_dataList = []
    m_fieldNo = {}
    m_fieldNames = ['proId', 'keywordId']
    m_fieldCNNames = []
    m_fieldDefVals = ['','']
    fi_key = -1
    [COL_PROID, COL_LABELID] = [i for i in range(2)]
    
    pro_label = {} #{proId:set([labelId...]), }
    label_pro = {} #{labelId:set([proId...], }
    
    @classmethod
    def populateClsData(cls):
        for obj in cls.getObjList():
            proId = obj.getValue(obj.COL_PROID)
            labelId = obj.getValue(obj.COL_LABELID)
            
            if not cls.pro_label.has_key(proId):
                cls.pro_label[proId] = set()
            
            #if labelId not in cls.pro_label[proId]:
            cls.pro_label[proId].add(labelId)
                
            if not cls.label_pro.has_key(labelId):
                cls.label_pro[labelId] = set()
                
            #if proId not in cls.label_pro[labelId]:
            cls.label_pro[labelId].add(proId)
        
    @classmethod
    def getProId(cls, labelIdList, bAnd=True):
        '''bAnd  - True 符合全部label的pro
                - False 只要符合labels之一的pro'''

        proIdset = set()
        if (bAnd):
            for labelId in labelIdList:
                if proIdset:
                    proIdset &= cls.label_pro[labelId]
                else:
                    proIdset = cls.label_pro[labelId]
        else:
            for labelId in labelIdList:
                if proIdset:
                    proIdset |= cls.label_pro[labelId]
                else:
                    proIdset = cls.label_pro[labelId]
            
        return list(proIdset)
##            for obj in cls.getObjList():
##                if obj.getValue(obj.COL_LABELID) in labelIdList:
##                    if not obj.getValue(obj.COL_PROID) in proIds
##                        proIds.append(obj.getValue(obj.COL_PROID))
##            sql = 'keywordId in (' + self.concat(keywordIds, ',') +')'
##            proLabelObjs = CDataProLabel.fetch(sql)
##            for obj in proLabelObjs:
##                proIds.append(obj.getValue(CDataProLabel.COL_PROID))
                
        return proIds
        
    @classmethod
    def getKeywordObjList(cls, proId):
        '''fetch keyword obj list by product'''
        #
        sql = '%s=%d' %(cls.m_fieldNames[0], proId)
        data = cls.fetchData(sql, [cls.COL_LABELID])
        if (len(data) == 0):
            return []
        
        #data is tuple's tuple ((),) -->(,)
        dataList = []
        for item in data:
            dataList.append(str(item[0]))
        sql = 'id in (' 
        sql += ','.join(dataList)  #long element
        #sql += str(tuple(dataList)) #[1] -> (1,)
        sql += ')'
        return CDataKeyword.fetch(sql)
      

class CDataCustomer(CDataModel):
    tbl = u'customer'
    m_dataList = []
    m_fieldNo = {}
##    m_fieldNames = ['id','name','briefCode','addr','email',
##                    'fax','linkman','lm_phone',
##                    'lm_memo','debt','memo']
    m_fieldCNNames = [u'客户编号',u'客户名称',u'助记码',u'联系地址',u'电子邮件',
                    u'传真',u'备注',u'联系人',u'联系人电话',
                    u'联系人备注',u'应收货款', u'', u'',u'类型',u'打印名称',
                    u'最近订单'
                    ,u'销售等级',u'潜力等级',u'沟通难度',u'回款速度'
                    ,u'友好度',u'微信',u'沟通偏好',u'联系间隔',u'最近联系'
                    ,u'拓新员工',u'业务专员',u'创建日期',u'上期欠款',u'结算时间']
    
    listenerList = []
    m_fieldDefVals = []
##    [COL_ID,COL_NAME,COL_BREIFCODE,COL_ADDR, COL_EMAIL,COL_FAX,
##    COL_LINKMAN,COL_LMPHONE,COL_LMMEMO,COL_DEBT,COL_MEMO] = [i for i in range(11)]
    
##    def __init__(self):    
##        CDataModel.__init__(self)
        #初始化实例对象时，应该初始化其数据，主要目的是初始化非字符串对象的数据类型
        #除非在数据模型中存在的全部是string类型
        #140704 新增客户时，初始化其id，以便其他步骤取用
        #       newAutoIncrId()从数据库中查找id，因此要避免同时初始化多个同类对象
        #       会导致id冲突
        # 140711 每次新建对象申请id,会导致导入数据库存在大量没有必要的操作
        #        所以为之新增函数
        #id = self.newAutoIncrId()
       
        #self.m_fieldVals = [-1, '','','','','','','','','','',1,'','','']
##        self.m_fieldVals = copy.deepcopy(self.m_fieldDefVals)
               
    @classmethod
    def makeField(cls):
        '''系统启动时调用子类的该函数完成初始化'''
        #cls.m_fieldNames = []
        #cls.m_fieldDefVals = []
        cls.COL_ID = cls.appendField('id', defVal=-1)
        cls.COL_NAME = cls.appendField('name')
        cls.COL_BREIFCODE = cls.appendField('briefCode')
        cls.COL_ADDR = cls.appendField('addr')
        cls.COL_EMAIL = cls.appendField('email')
        cls.COL_FAX = cls.appendField('fax')
        cls.COL_LINKMAN = cls.appendField('linkman')
        cls.COL_LMPHONE = cls.appendField('lm_phone')
        cls.COL_LMMEMO = cls.appendField('lm_memo')
        cls.COL_DEBT = cls.appendField('debt')
        cls.COL_MEMO = cls.appendField('memo')
        cls.COL_REGION = cls.appendField('region', defVal=1)
        cls.COL_TYPE = cls.appendField('type')
        cls.COL_PRINTNAME = cls.appendField('printName')
        cls.COL_LASTORDER = cls.appendField('lastOrder', defVal=None)
        cls.COL_LVL_SALES = cls.appendField('lvl_sales')
        cls.COL_LVL_POTENTIAL = cls.appendField('lvl_potential')
        cls.COL_LVL_DIFFICULT = cls.appendField('lvl_difficult')
        cls.COL_LVL_PAYBACK = cls.appendField('lvl_payback')
        cls.COL_LVL_LOYALTY = cls.appendField('lvl_loyalty')
        cls.COL_WECHAT = cls.appendField('wechat')
        cls.COL_COMM_TYPE = cls.appendField('comm_type')
        cls.COL_CONTACT_INTERVAL = cls.appendField('contact_interval', defVal=0)
        cls.COL_LAST_CONTACT = cls.appendField('last_contact', defVal=None)
        cls.COL_PIONEER = cls.appendField('pioneer', defVal=None)
        cls.COL_SALESMAN = cls.appendField('salesman', defVal=None)
        cls.COL_CREATE_TIME = cls.appendField('create_time', defVal=None)
        cls.COL_PRE_ARREARS = cls.appendField('pre_arrears', defVal=None)
        cls.COL_SETTLE_DATE = cls.appendField('settle_date', defVal=None)
        
        #cls.refreshDataList()
        
        CDataVoucher.attach(cls.updateExtraByVoucher)
        CDataSaleOrder.attach(cls.updateExtraByOrder)
    
    def assignNewId(self):
        id = self.newAutoIncrId()
        self.setValue(self.COL_ID, id)
        return id
    
    def populateExtra(self):
        # 应付直接从数据中读取，不要再缓存了，太慢
        self.overDue = True
    
    def getDebtFromDB(self):
        #获取欠款情况：应收货款总金额-已收货款总金额+期初欠款
        #self.debt = self.getSaleAmount() - self.getPaidAmount() + self.m_fieldVals[self.COL_DEBT]
        sql = '''SELECT debt+IFNULL(sales,0)-IFNULL(paid,0) 
                FROM customer
                    LEFT JOIN (SELECT peerId, SUM(total-discount) as sales from saleorder WHERE peerId=%d) T1 ON customer.id=T1.peerId
                    LEFT JOIN (SELECT peerId, SUM(money) as paid FROM voucher  WHERE peerId=%d)  T2 ON customer.id=T2.peerId
                WHERE customer.id=%d''' %(self.getValue(self.COL_ID),self.getValue(self.COL_ID),self.getValue(self.COL_ID))
        # 240826 - 本来打算改成从对账归结日期 settle_date 开始计算，但是怕归结操作出错，还是看总账吧
        cnt = dbmngr.dbmngr.execute(sql)
        self.debt =  dbmngr.dbmngr.cursor.fetchone()[0]
        if abs(self.debt) < 0.01:
            self.debt = 0.0
        self.overDue = False
    
    @classmethod
    def updateExtraByVoucher(cls, op, voucher):
        #没必要立刻更新，只需要overDue，然后使用时更新即可
        id = voucher.getValue(voucher.COL_PEERID)
        print ('notify ', op, id)
        obj = cls.findObj(id)
        obj.overDue = True
        
        # 不必再进行notify，一则防止递归，二则没必要（因为过时的是extraData，
        # 除非是notify界面，但是现在还没有设计）
        #obj.notify()
    
    @classmethod    
    def updateExtraByOrder(cls, op, order):
        id = order.getValue(order.COL_PEERID)
        print ('notify ', op, id)
        obj = cls.findObj(id)
        obj.overDue = True
        
        #obj.notify()
                
    def getDebt(self):
        if self.overDue:
            #self.populateExtra()
            self.getDebtFromDB()
        return self.debt
    
    def getInitDebt(self):
        return self.m_fieldVals[self.COL_DEBT]
    
    def getSaleAmount(self):
        custId = self.getValue(self.COL_ID)
        return CDataSaleOrder.getCustSaleAmount(custId)
    
    def getPaidAmount(self):
        custId = self.getValue(self.COL_ID)
        return CDataVoucher.getCustPaid(custId)
    
    @classmethod
    def getName(cls, id):

        if id is None:
            return ""
        obj = cls.getData({"id":id})
        return obj.getValue(cls.COL_NAME) if obj is not None else ""
    
    @classmethod
    def getPrintName(cls, id):

        if id is None:
            return ""
        obj = cls.getData({"id":id})
        if obj is not None:
            return obj.getValue(cls.COL_NAME) if obj.getValue(cls.COL_PRINTNAME).strip() == "" else obj.getValue(cls.COL_PRINTNAME)
        return ""


    @classmethod
    def getProDetail(cls, custId, dateStart):
        return CDataSaleDetail.getCustomerExpense(custId, dateStart)

    @classmethod
    def GetLatestProList(cls, custId, billStart, filterSql=''):#categorys=None):

        sql = '''SELECT last_billid
                    ,d.proId
                    ,d.proName
                    ,p.spec
                    ,d.unit
                    ,d.price as latest_price
                    ,p.onhand1
                    ,p.position                    
                    ,sum_qty
                    ,sum_total
                    ,if(onUse=1,'O','X')
                    ,lowest_qty
                FROM saledetails d
                LEFT JOIN
                    (SELECT proId as id,SUM(total) as sum_total,SUM(num) as sum_qty,MAX(d.billId) as last_billid
                    FROM saledetails d
                        LEFT JOIN customer c ON c.id=d.peerId
                    WHERE c.id=%d
                    GROUP BY proId) T ON T.id=d.proId 
                LEFT JOIN customer c ON c.id=d.peerId
                LEFT JOIN product p ON p.id=d.proId
                WHERE c.id=%d AND d.billId > "%s" AND last_billid=d.billId %s
                GROUP BY d.proId
                ORDER BY d.proName DESC''' %(custId, custId
                        , billStart
                        , " and %s " %(filterSql) if (len(filterSql)>0) else "")
                        #, " categoryId in (%s)"%(",".join(str(e) for e in categorys)) if (categorys is not None and len(categorys)>0) else "") 
        print sql 
        cnt = dbmngr.dbmngr.execute(sql)
        print 'cnt %d' %cnt
        if (cnt != 0): 
            return dbmngr.dbmngr.cursor.fetchall()
        else:
            return []


    def getRegion(self):
        regionId = self.getValue(self.COL_REGION)
        regionObj = CDataCustomerRegion.getData({"id":regionId})
        return regionObj.getValue(regionObj.fi_name)
    
    def getType(self):
        return CDataCustomerType.getData({"id":self.getValue(self.COL_TYPE)}).getValue(CDataCustomerType.fi_name)
    
    @classmethod
    def getSearchSql(cls, val, prefix=""):
        str = "'%%%s%%'"  %val
        sql = ''
        # 'lastOrder'是Date类型，like报错1271
        # for field in CDataCustomer.m_fieldNames:
        #     if (field in ['lastOrder', 'last_contact', 'create_time']):
        #         continue
        #     sql += '%s like %s or ' %(field, str)
        for field in ['name','briefCode','addr','email','fax','linkman','lm_phone','lm_memo','memo','printName']:
            sql += '%s%s like %s or ' %(prefix, field, str)
        return sql[:-4]
    
    @classmethod
    def getStat(cls, dateStart, dateEnd): 
        '''未启用'''
        sql = 'select sum(total),sum(discount) from %s group by peerid'
    
    @classmethod
    def hasUnalloctVoucher(self):
        '''检查是否有未分配的收款单'''
        pass
        
    @staticmethod
    def getRouteList(planId, routeId, only_wujindian, only_alarm_customer, only_send_debt):
        ''' 读取路线客户列表 '''

        sqlWhere = "%s %s %s "\
            " region in (select regionId from delivery_route_region where routeId=%d)"\
            %( "type=4 and" if only_wujindian else ""
                ,"sales-paid+c.debt-ifnull(unsend_sales,0)>0 and" if only_send_debt else ""
                ,"""(lastOrder is null
                    or (lvl_sales='A' AND DATEDIFF(CURDATE(),lastOrder)>30) 
                    or (lvl_sales='B' AND DATEDIFF(CURDATE(),lastOrder)>60) 
                    or (lvl_sales='C' AND DATEDIFF(CURDATE(),lastOrder)>90)) and """ if only_alarm_customer else ""
                ,routeId)    
        return CDataCustomer.__getRegionList(sqlWhere, planId)
        
        
    @staticmethod
    def getRegionList(planId, regionId, only_wujindian, only_alarm_customer, only_send_debt):
        ''' 读取所属地区的客户列表 '''
        sqlWhere = "%s %s %s "\
            " region=%d " \
            %("type=4 and" if only_wujindian else ""
            ,"sales-paid+c.debt-ifnull(unsend_sales,0)>0 and " if only_send_debt else ""
            ,"""(lastOrder is null
                or (lvl_sales='A' AND DATEDIFF(CURDATE(),lastOrder)>30) 
                or (lvl_sales='B' AND DATEDIFF(CURDATE(),lastOrder)>60) 
                or (lvl_sales='C' AND DATEDIFF(CURDATE(),lastOrder)>90)) and """ if only_alarm_customer else ""
            ,regionId)    
        return CDataCustomer.__getRegionList(sqlWhere, planId)

    @staticmethod
    def getCustomerList(sqlLike, regionId, only_wujindian, only_alarm_customer, only_send_debt):
        ''' 读取所有客户列表 '''
        sqlWhere = " %s %s %s %s %s "\
            %("type=4 and" if only_wujindian else ""
            ,"ifnull(sales,0)-ifnull(paid,0)+c.debt-ifnull(unsend_sales,0)>0 and " if only_send_debt else ""
            ,"""(lastOrder is null
                or (lvl_sales='A' AND DATEDIFF(CURDATE(),lastOrder)>30) 
                or (lvl_sales='B' AND DATEDIFF(CURDATE(),lastOrder)>60) 
                or (lvl_sales='C' AND DATEDIFF(CURDATE(),lastOrder)>90)) and """ if only_alarm_customer else ""
            ," region=%d and "%regionId if regionId else ""
            ,sqlLike or " 1=1 ")

        sql = "select c.id,c.name"\
                ",if(trim(c.printName) != '',c.printName,c.name)"\
                ",customer_type.name"\
                ",customer_region.name"\
                ",CONCAT(date_format(lastOrder, '%%Y-%%m-%%d'),'(',DATEDIFF(CURDATE(),lastOrder),')')"\
                ",linkman"\
                ",lm_phone "\
                ",email"\
                ",fax"\
                ",if(lvl_sales!='', lvl_sales, '~')"\
                ",wechat"\
                ",debt"\
                ",if(abs(ifnull(sales,0)-ifnull(paid,0)+c.debt)<0.01,0,ifnull(sales,0)-ifnull(paid,0)+c.debt)"\
                ",c.lm_memo "\
                ",c.memo"\
                ",ifnull(unsend_sales,0)"\
                ",if(abs(ifnull(sales,0)-ifnull(paid,0)+c.debt-ifnull(unsend_sales,0))<0.01,0,ifnull(sales,0)-ifnull(paid,0)+c.debt-ifnull(unsend_sales,0))"\
                ",CONCAT(staff.name, date_format(create_time, '%%y%%m%%d'))"\
                ",IF(DATEDIFF(CURDATE(),lastOrder) <= contact_interval or DATEDIFF(CURDATE(),last_contact) <= contact_interval,0,1) "\
                " from customer c "\
                "  left join (select peerId, sum(total)-sum(discount) as sales from saleorder group by peerId) t1 on t1.peerId=c.id "\
                "  left join (select peerId, sum(money) as paid from voucher group by peerId) t2 on c.id=t2.peerId "\
                "  left join (select peerId, sum(total)-sum(discount) as unsend_sales from saleorder where state='' group by peerId) t3 on t3.peerId=c.id "\
                "  left join customer_type on type=customer_type.Id"\
                "  left join customer_region on region=customer_region.Id"\
                "  left join staff on pioneer=staff.id"\
                " where %s "\
                " order by lastOrder desc" %(sqlWhere) 
        print sql 
        cnt = dbmngr.dbmngr.execute(sql)
        print 'cnt %d' %cnt
        if (cnt != 0): 
            return dbmngr.dbmngr.cursor.fetchall()
        else:
            return []

    #",IFNULL(lastOrder,'')"\
    @staticmethod
    def __getRegionList(sqlWhere, planId):    
        sql = "select c.id,name"\
                ",if(abs(sales-paid+c.debt)<0.01,0,sales-paid+c.debt)"\
                ",ifnull(unsend_sales,0)"\
                ",if(abs(sales-paid+c.debt-ifnull(unsend_sales,0))<0.01,0,sales-paid+c.debt-ifnull(unsend_sales,0))"\
                ",CONCAT(date_format(lastOrder, '%%y%%m%%d'),'(',DATEDIFF(CURDATE(),lastOrder),'days)')"\
                ",lm_phone "\
                ",if(lvl_sales='','~',lvl_sales)"\
                ",if(b_wechat=1,'O','') "\
                ",if(b_phone=1,'O','') "\
                ",if(b_order=1,'O','') "\
                ",if(d.delivery_id='%s',d.memo,'') "\
                ",c.lm_memo "\
                "from customer c "\
                "  left join delivery_customer_contact d on d.customer_id=c.id " \
                "  left join (select peerId, sum(total)-sum(discount) as sales from saleorder group by peerId) t1 on t1.peerId=c.id "\
                "  left join (select peerId, sum(money) as paid from voucher group by peerId) t2 on c.id=t2.peerId "\
                "  left join (select peerId, sum(total)-sum(discount) as unsend_sales from saleorder where state='' group by peerId) t3 on t3.peerId=c.id "\
                "where %s "\
                "order by lastOrder desc" %(planId, sqlWhere) 
        print sql 
        cnt = dbmngr.dbmngr.execute(sql)
        print 'cnt %d' %cnt
        if (cnt != 0): 
            return dbmngr.dbmngr.cursor.fetchall()
        else:
            return []
    
    @staticmethod
    def AddLog(customer_id, reporter_id, reporter_name, content, delivery_id, report_time):
    
        if delivery_id is None:
            sql = "insert into customer_log "\
                "(customer_id, report_time,reporter_id,reporter_name,content,creator_id,create_time)"\
                "values (%d,'%s',%d,'%s','%s',%d,now()) "\
                %(customer_id, report_time, reporter_id, reporter_name,content
                ,config.Cfg.getCurUser().getValue(CDataStaff.COL_ID))
            dbmngr.dbmngr.execute(sql)
            
            peerObj = CDataCustomer.getData({'id':customer_id})
            if (peerObj is not None):
                log = report_time[5:10]+content
                peerObj.setValue(peerObj.COL_LMMEMO, log)
                sql = "update customer set lm_memo='%s' where id=%d" %(log, customer_id)
                dbmngr.dbmngr.execute(sql)
            
        else:
            sql = "select id from customer_log "\
                "where customer_id=%d and delivery_id='%s' "\
                %(customer_id, delivery_id)
            log_id = dbmngr.dbmngr.queryValue(sql)
            if (log_id is None):
                sql = "insert into customer_log "\
                    "(customer_id, report_time,reporter_id,reporter_name,content,creator_id,create_time,delivery_id)"\
                    "values (%d,'%s',%d,'%s','%s',%d,now(),'%s') "\
                    %(customer_id, report_time, reporter_id, reporter_name,content
                    ,config.Cfg.getCurUser().getValue(CDataStaff.COL_ID)
                    #,config.Cfg.getCurUser().getValue(CDataStaff.COL_NAME)
                    ,delivery_id)
            else:
                sql = "update customer_log set "\
                    " report_time='%s'"\
                    ",reporter_id=%d"\
                    ",reporter_name='%s'"\
                    ",content='%s'"\
                    ",creator_id=%d"\
                    ",create_time=now()"\
                    " where id=%d" \
                    %(report_time, reporter_id, reporter_name,content
                    ,config.Cfg.getCurUser().getValue(CDataStaff.COL_ID)
                    #,config.Cfg.getCurUser().getValue(CDataStaff.COL_NAME)
                    ,log_id)
                    
            dbmngr.dbmngr.execute(sql)
    
        

    @staticmethod
    def GetLogContent(customer_id, delivery_id):
        
        sql = "select content from customer_log "\
                "where customer_id=%d and delivery_id='%s' "\
                %(customer_id, delivery_id)
        return dbmngr.dbmngr.queryValue(sql, "")
            
    @staticmethod
    def get_log_list(dateStart, dateEnd, peerId):
        
        sql = "select customer_log.id,customer_id,customer.name,customer_region.name,reporter_name,report_time,content "\
            " from customer_log "\
            "   left join customer on customer_id=customer.id"\
            "   left join customer_region on region=customer_region.Id"\
            " where date_format(report_time, '%%Y-%%m-%%d') >= '%s' "\
            "   and date_format(report_time, '%%Y-%%m-%%d') <= '%s' "\
            "   %s "\
            " order by report_time desc" %(dateStart, dateEnd, "and customer_id=%d" %peerId if peerId is not None else "")
        
        print sql 
        cnt = dbmngr.dbmngr.execute(sql)
        print 'cnt %d' %cnt
        if (cnt != 0): 
            return dbmngr.dbmngr.cursor.fetchall()
        else:
            return []
    
    @staticmethod
    def query_overdue(regionId=None, salesman=None, show_all=False, only_show_delivery=False):
        '''查询超期联系客户'''

        sql = "SELECT c.id,c.name,r.name,"\
            "CONCAT(date_format(lastOrder, '%Y-%m-%d'),'(',DATEDIFF(CURDATE(),lastOrder),'天前)'),"\
            "c.lm_memo,"\
            "IF (c.lvl_sales!='', CONCAT(c.lvl_sales,c.lvl_payback,c.lvl_loyalty,'/',lvl_potential), ''),"\
            "c.lvl_difficult,"\
            "c.lm_phone,"\
            "c.email,"\
            "c.wechat,"\
            "c.memo,"\
            "contact_interval,"\
            "c.region,"\
            "s.name"\
            " FROM customer c "\
            "   LEFT JOIN customer_region r ON c.region=r.Id "\
            "   LEFT JOIN staff s ON s.id=r.salesman "\
            " WHERE type=4 "

        if regionId is not None:
            sql += " AND c.region=%s " %(regionId)

        if salesman is not None:
            sql += " AND r.salesman=%s " %(salesman)

        if not show_all:
            sql += " AND contact_interval>0 "\
                "    AND ((DATEDIFF(CURDATE(),lastOrder) > contact_interval "\
                "       AND DATEDIFF(CURDATE(),last_contact) > contact_interval) "\
                "       OR last_contact is NULL)"

        if only_show_delivery:
            sql += " AND c.region in (select distinct regionId from delivery_route_region)"

        cnt = dbmngr.dbmngr.execute(sql)
        print sql
        print 'cnt %d' %cnt
        if (cnt != 0): 
            return dbmngr.dbmngr.cursor.fetchall()
        else:
            return [] 

    def IsOverdue(self):

        today = datetime.datetime.today().date()
        interval = self.m_fieldVals[self.COL_CONTACT_INTERVAL] + (10 if today.month > 6 and today.month < 10 else 0)
        
        if (self.m_fieldVals[self.COL_ID] == 2):
            print("IsOverdue:",interval,self.m_fieldVals[self.COL_LASTORDER],self.m_fieldVals[self.COL_LAST_CONTACT])

        if (self.m_fieldVals[self.COL_TYPE] == 4
            and interval > 0
            and (self.m_fieldVals[self.COL_LASTORDER] is None 
                or today - self.m_fieldVals[self.COL_LASTORDER] 
                    > datetime.timedelta(interval))
            and (self.m_fieldVals[self.COL_LAST_CONTACT] is None 
                or today - self.m_fieldVals[self.COL_LAST_CONTACT] 
                    > datetime.timedelta(interval))):
            return True

        return False

    @staticmethod
    def query_year_sales(customer_id):
        
        sql = "SELECT year(billDate) as yy,ROUND(SUM(total-discount)) "\
                " FROM saleorder "\
                " WHERE peerId=%d "\
                " GROUP BY yy "\
                " ORDER BY yy DESC "\
                %(customer_id)
        
        cnt = dbmngr.dbmngr.execute(sql)
        print sql
        print 'cnt %d' %cnt
        if (cnt != 0): 
            return dbmngr.dbmngr.cursor.fetchall()
        else:
            return []

    @staticmethod
    def query_stat_som(filter_sql):
        sql = u"""SELECT customer.id
                ,customer.name
                ,CONCAT(date_format(lastOrder, '%%y%%m%%d'),'(',DATEDIFF(CURDATE(),lastOrder),'天前)')
                ,if (abs(debt+IFNULL(sales,0)-IFNULL(paid,0))<0.01, 0, debt+IFNULL(sales,0)-IFNULL(paid,0))
                ,wechat
                ,if(lvl_sales!="", lvl_sales, '~')
                ,CONVERT(lvl_potential, DECIMAL) as eval_sale
                    ,ROUND(SUM(total-discount),0) as actual_sale	
                ,ROUND(SUM(total-discount) / CONVERT(lvl_potential, DECIMAL),2)
                ,lm_memo
                ,customer.memo
                ,CONCAT(staff.name, date_format(create_time, '%%y%%m%%d'))
                ,IF(DATEDIFF(CURDATE(),lastOrder) <= contact_interval or DATEDIFF(CURDATE(),last_contact) <= contact_interval,0,1)
                FROM customer 
                LEFT JOIN saleorder ON saleorder.peerId=customer.id AND (DATEDIFF(CURDATE(),billDate)<365 OR billDate is NULL) 
                LEFT JOIN staff ON pioneer=staff.id
                LEFT JOIN (SELECT peerId, SUM(total-discount) as sales from saleorder GROUP BY peerId) T1 ON customer.id=T1.peerId
                LEFT JOIN (SELECT peerId, SUM(money) as paid FROM voucher  GROUP BY peerId)  T2 ON customer.id=T2.peerId
                WHERE %s
                GROUP BY customer.id""" %(filter_sql)
        
        print sql
        cnt = dbmngr.dbmngr.execute(sql)        
        print 'cnt %d' %cnt
        if (cnt != 0): 
            return dbmngr.dbmngr.cursor.fetchall()
        else:
            return []

    def UpdateSettleInfo(self):
        sql = '''update customer set pre_arrears=%f,settle_date='%s' where id=%d'''%(self.getValue(self.COL_PRE_ARREARS),
            self.getValue(self.COL_SETTLE_DATE), self.getValue(self.COL_ID))

        dbmngr.dbmngr.execute(sql)

    def updateMemoPic(self, pic_num):

        if (pic_num < 2):
            sql = '''update customer set memo=CONCAT('[%dp]', memo) where id=%d'''%(pic_num
                ,self.getValue(self.COL_ID))
            dbmngr.dbmngr.execute(sql)
        # else:
        #     sql = '''update customer set memo=REGEXP_REPLACE(memo, '^\\[\dp]', '')  where id=%d'''%(pic_num
        #         ,self.getValue(self.COL_ID))
        #     dbmngr.dbmngr.execute(sql)


    def getCheckProDetails(self, check_all=True):
        ''' 对账读取商品明细 '''
        if (check_all or self.getValue(self.COL_SETTLE_DATE) is None):
            sql = """SET @running_total := (SELECT debt FROM customer WHERE id=%d);""" %(self.getValue(self.COL_ID))
            sql_settle = ''
            sql_voucher = ''
        else:
            sql = """SET @running_total := (SELECT pre_arrears FROM customer WHERE id=%d);""" %(self.getValue(self.COL_ID))
            sql_settle = ' AND billDate>"%s" ' %(str(self.getValue(self.COL_SETTLE_DATE)))
            sql_voucher = ' AND date>"%s" ' %(str(self.getValue(self.COL_SETTLE_DATE))) 
        dbmngr.dbmngr.execute(sql)

        # sql = """SELECT billId
        #             ,proName
        #             ,spec
        #             ,unit
        #             ,num
        #             ,price
        #             ,saledetails.total
        #             ,@running_total := @running_total + saledetails.total AS balance
        #             ,saledetails.memo
        #         FROM saledetails
        #             LEFT JOIN product ON saledetails.proId=product.id
        #             LEFT JOIN saleorder ON saledetails.billId=saleorder.id
        #         WHERE saledetails.peerId = %d %s
        #         ORDER BY billId ASC;""" %(self.getValue(self.COL_ID)
        #         ,sql_settle)
        # 240830 加入收款单
        sql = """SELECT billId
                        ,billDate
                        ,proName
                        ,spec
                        ,unit
                        ,num
                        ,price
                        ,ROUND(total,2) as total
                        ,@running_total := @running_total + total AS balance
                        ,memo
                        ,bill_type
                FROM (SELECT billId
                        ,billDate
                        ,proName
                        ,spec
                        ,unit
                        ,num
                        ,price
                        ,ROUND(saledetails.total,2) as total
                        ,saledetails.memo
                        ,0 as bill_type
                    FROM saledetails
                        LEFT JOIN product ON saledetails.proId=product.id
                        LEFT JOIN saleorder ON saledetails.billId=saleorder.id
                    WHERE saledetails.peerId = %d %s

                    UNION

                    SELECT voucher.id
                        ,date
                        ,account.briefName
                        ,'**********'
                        ,''
                        ,''
                        ,''
                        ,-money
                        ,''
                        ,1 as bill_type
                    FROM voucher LEFT JOIN account ON accountId=account.id
                    WHERE voucher.peerId = %d %s) T

                ORDER BY billDate ASC,bill_type ASC  ;
        """%(self.getValue(self.COL_ID)
            ,sql_settle
            ,self.getValue(self.COL_ID)
            ,sql_voucher)

        print (sql)
        cnt = dbmngr.dbmngr.execute(sql)
        print ('cnt %d' %cnt)
        if (cnt != 0): 
            return dbmngr.dbmngr.cursor.fetchall()
        else:
            return []

    def HasUnsendOrder(self):
        ''' 检查是否有已经开单 '''

        sql = '''SELECT count(*)
                FROM saleorder O
                    LEFT JOIN delivery_plan D ON D.Id=O.deliveryPlan
                WHERE peerId=%d AND total>0 AND O.state="" AND (D.state IS NULL OR D.state=1)
            ''' %(self.getValue(self.COL_ID))
        cnt = dbmngr.dbmngr.execute(sql)
        return dbmngr.dbmngr.cursor.fetchone()[0]

class CDataCustomerContact(CDataModel):
    tbl = 'customer_contact'
    m_dataList = []
    m_fieldDefVals = []
##    def __init__(self):
##        CDataModel.__init__(self)
##        self.m_fieldVals = [''] * len(self.m_fieldNames)
    
    @classmethod
    def makeField(cls):
        '''系统启动时调用子类的该函数完成初始化'''
        #cls.m_fieldNames = []
        #cls.m_fieldCNNames = []
        cls.iId = cls.appendField('id', u'编号')
        cls.iPeerId = cls.appendField('peerId')
        cls.iName = cls.appendField('name', u'联系人名称')
        cls.iPhone = cls.appendField('phone', u'联系人电话')
        cls.iMemo = cls.appendField('memo', u'信息备注')
        
        cls.refreshDataList()
       
    def getText(self):
        name = self.getValue(self.iName)
        phone = self.getValue(self.iPhone)
        if(phone != ''):
            str = name + ',' + phone
        else:
            str = name 
        return str
    
    @classmethod
    def getContactText(cls, peerId):
        data = []
        for obj in cls.m_dataList:
            if(peerId == obj.getValue(cls.iPeerId)):
                data.append(obj.getText())
        return data

class CDataCustomerLabel(CDataModel):
    tbl = 'customer_label'
    m_dataList = []
    m_fieldDefVals = []
    @classmethod
    def makeField(cls):
        '''系统启动时调用子类的该函数完成初始化'''
        #cls.m_fieldNames = []
        cls.fi_id = cls.appendField('id')
        cls.fi_name = cls.appendField('name')
        
        cls.refreshDataList()
        
    @classmethod
    def getNames(cls):
        ret = []
        for obj in cls.m_dataList:
            ret.append(obj.getValue(obj.fi_name))
            
        return ret
        
    @classmethod
    def add(cls, name):
        obj = cls.factory()
        obj.setValue(cls.fi_name, name)
        return obj.insert()
        
class CDataCustomerLabeling(CDataModel):
    tbl = 'customer_labeling'
    m_dataList = []
    fi_key = -1
    m_fieldDefVals = []
    @classmethod
    def makeField(cls):
        '''系统启动时调用子类的该函数完成初始化'''
        #cls.m_fieldNames = []
        cls.fi_custId = cls.appendField('custId')
        cls.fi_labelId = cls.appendField('labelId')
        
        #cls.refreshDataList()
    
    @classmethod
    def getKeyword(cls, custId):
        sql = 'select name from %s where id in (select labelId from %s where custId=%d)' \
                %(CDataCustomerLabel.tbl, cls.tbl, custId)
                
        cnt = dbmngr.dbmngr.execute(sql)
        if (cnt == 0):
            return []
        
        result = dbmngr.dbmngr.cursor.fetchall() 
        list = []
        for item in result:
            list.append(item[0])
        return list
    
class CDataCustomerRegion(CDataModel):
    tbl = 'customer_region'
    m_dataList = []
    m_fieldDefVals = []
    
    @classmethod
    def makeField(cls):
        '''系统启动时调用子类的该函数完成初始化'''
        #cls.m_fieldNames = []
        #cls.m_fieldCNNames = []
        cls.fi_id = cls.appendField('id', u'编号')
        cls.fi_name = cls.appendField('name', u'地区名称')
        cls.fi_code = cls.appendField('code', u'简码', defVal='')
        cls.fi_memo = cls.appendField('memo', u'说明', defVal='')
        cls.fi_lastDelivery = cls.appendField('last_delivery', u'最近送货时间', defVal=None)
        cls.fi_delivery_interval = cls.appendField('delivery_interval', u'送货间隔', defVal=30)
        cls.fi_level = cls.appendField('level', u'等级', defVal='')
        cls.fi_distance = cls.appendField('distance', u'导航距离', defVal='')
        cls.fi_drive_time = cls.appendField('drive_time', u'导航时间', defVal='')
        cls.fi_market_share = cls.appendField('market_share', u'我方份额', defVal='')
        cls.fi_salesman = cls.appendField('salesman', u'开发专员', defVal=None)
        cls.fi_importance = cls.appendField('importance', u'重要性', defVal='')
        cls.fi_route = cls.appendField('route', u'适宜路线', defVal='')

        cls.refreshDataList()
    
    @classmethod
    def getName(cls, id):
        obj = cls.findObj(id)
        return obj.getValue(obj.fi_name) 
        
##    @classmethod
##    def createNew(cls):
##        obj = cls()
##        obj.setValue(cls.fi_id, cls.newAutoIncrId())
##        return obj
   
    @classmethod
    def add(cls, name, memo, code, span, level):
        import config
        obj = cls.factory()
        obj.setValue(cls.fi_name, name)
        if not code:
            code = config.CBriefCode.getBriefCode(name)
        obj.setValue(cls.fi_code, code)
        obj.setValue(cls.fi_memo, memo)
        obj.setValue(cls.fi_delivery_interval, span)
        obj.setValue(cls.fi_level, level)
        return obj.insert()
    
    @classmethod        
    def getDetault(cls):
        return cls.getFirstInstance({cls.fi_id:1})
    
    @classmethod
    def getRegionList(cls, only_wujindian=True):
        sql = "select r.Id,0,count(c.id),r.name,r.last_delivery "\
            ",sum(if(DATEDIFF(CURDATE(),c.lastOrder) <= 180,1,0)) as active_customer "\
            "from customer_region r "\
            "left join customer c on r.Id=c.region %s "\
            "group by r.Id " %("and c.type=4" if only_wujindian else "")
        cnt = dbmngr.dbmngr.execute(sql)
        return dbmngr.dbmngr.cursor.fetchall()
    
    @classmethod
    def getRegionSom(cls, only_wujindian=True):
        sql = "select r.Id,0,count(T.id),r.name,r.last_delivery "\
            ",sum(if(DATEDIFF(CURDATE(),T.lastOrder) <= 180,1,0)) as active_customer "\
            ",ROUND(SUM(eval_sale),0) "\
            ",CONVERT(ROUND(SUM(actual_sale),0), DECIMAL) "\
            ",ROUND(SUM(actual_sale) / SUM(eval_sale),2) "\
            "from customer_region r "\
            " LEFT JOIN (SELECT customer.id,customer.region, SUM(total-discount) as actual_sale, CONVERT(lvl_potential, DECIMAL) as eval_sale,lastOrder "\
            "   FROM customer LEFT JOIN saleorder ON saleorder.peerId=customer.id AND (DATEDIFF(CURDATE(),billDate)<365 OR billDate is NULL) "\
            "   WHERE 1=1 %s "\
            "   GROUP BY customer.id) T ON T.region=r.Id "\
            "group by r.Id " %("and type=4" if only_wujindian else "")
        cnt = dbmngr.dbmngr.execute(sql)
        return dbmngr.dbmngr.cursor.fetchall()

    @classmethod
    def getRegionNoSendSom(cls):
        sql = "select r.Id,0,count(T.id),r.name,r.last_delivery "\
            ",sum(if(DATEDIFF(CURDATE(),T.lastOrder) <= 180,1,0)) as active_customer "\
            ",ROUND(SUM(eval_sale),0) "\
            ",CONVERT(ROUND(SUM(actual_sale),0), DECIMAL) "\
            ",ROUND(SUM(actual_sale) / SUM(eval_sale),2) "\
            "from customer_region r "\
            " LEFT JOIN (SELECT customer.id,customer.region, SUM(total-discount) as actual_sale, CONVERT(lvl_potential, DECIMAL) as eval_sale,lastOrder "\
            "   FROM customer LEFT JOIN saleorder ON saleorder.peerId=customer.id AND (DATEDIFF(CURDATE(),billDate)<365 OR billDate is NULL) "\
            "   WHERE type=4 AND region not IN (SELECT DISTINCT(regionId) FROM delivery_route_region) "\
            "   GROUP BY customer.id) T ON T.region=r.Id "\
            "WHERE r.Id not IN (SELECT DISTINCT(regionId) FROM delivery_route_region) "\
            "group by r.Id "
        cnt = dbmngr.dbmngr.execute(sql)
        return dbmngr.dbmngr.cursor.fetchall()

    @classmethod
    def getRegionAllSendSom(cls):
        sql = "select r.Id,0,count(T.id),r.name,r.last_delivery "\
            ",sum(if(DATEDIFF(CURDATE(),T.lastOrder) <= 180,1,0)) as active_customer "\
            ",ROUND(SUM(eval_sale),0) "\
            ",CONVERT(ROUND(SUM(actual_sale),0), DECIMAL) "\
            ",ROUND(SUM(actual_sale) / SUM(eval_sale),2) "\
            "from customer_region r "\
            " LEFT JOIN (SELECT customer.id,customer.region, SUM(total-discount) as actual_sale, CONVERT(lvl_potential, DECIMAL) as eval_sale,lastOrder "\
            "   FROM customer LEFT JOIN saleorder ON saleorder.peerId=customer.id AND (DATEDIFF(CURDATE(),billDate)<365 OR billDate is NULL) "\
            "   WHERE type=4 AND region IN (SELECT DISTINCT(regionId) FROM delivery_route_region) "\
            "   GROUP BY customer.id) T ON T.region=r.Id "\
            "WHERE r.Id IN (SELECT DISTINCT(regionId) FROM delivery_route_region) "\
            "group by r.Id "
        cnt = dbmngr.dbmngr.execute(sql)
        return dbmngr.dbmngr.cursor.fetchall()
        
    @staticmethod
    def query_overdue(salesman=None, show_all=False):
        '''查询超期地区'''

        sql = "SELECT r.Id,r.name,r.level,r.importance,r.delivery_interval"\
            ",CONCAT(date_format(r.last_delivery, '%Y-%m-%d'),'(',DATEDIFF(CURDATE(),r.last_delivery),'天前)')"\
            ",sum(if(DATEDIFF(CURDATE(),c.lastOrder) <= 180,1,0)),count(c.id)"\
            ",r.route"\
            ",r.distance"\
            ",r.drive_time"\
            ",r.market_share"\
            ",s.name"\
            ",r.memo"\
            " from customer_region r "\
            "   left join customer c on r.Id=c.region and c.type=4 "\
            "   left join staff s on r.salesman=s.id "\
            " where 1=1 "            

        if salesman is not None:
            sql += " AND r.salesman=%s" %(salesman)

        if not show_all:
            sql += " AND delivery_interval>0 "\
                "   AND DATEDIFF(CURDATE(),last_delivery) > delivery_interval "

        sql += " group by r.Id "

        cnt = dbmngr.dbmngr.execute(sql)
        print sql
        print 'cnt %d' %cnt
        if (cnt != 0): 
            return dbmngr.dbmngr.cursor.fetchall()
        else:
            return [] 

class CDataCustomerType(CDataModel):
    tbl = 'customer_type'
    m_dataList = []
    m_fieldDefVals = []
    @classmethod
    def makeField(cls):
        '''系统启动时调用子类的该函数完成初始化'''
        #cls.m_fieldNames = []
        cls.fi_id = cls.appendField('id', u'编号')
        cls.fi_name = cls.appendField('name', u'类型名称')
        
        cls.refreshDataList()
    
##    @classmethod
##    def createNew(cls):
##        obj = cls()
##        obj.setValue(cls.fi_id, cls.newAutoIncrId())
##        return obj
    
    @classmethod
    def add(cls, name):
        obj = cls.factory()
        obj.setValue(cls.fi_name, name)
        return obj.insert()

class CDataSaleDetail(CDataModel):
    tbl = 'saleDetails'
    m_dataList = []
    m_fieldNo = {}
    m_fieldNames = ['billId', 'peerId','proId','proName', 'unit','price', 'num', 'total', 'memo', "wh_from", "wh_to", "from_position"]
    m_fieldCNNames = ['saleId', 'custId', 'proId','proName','unit', 'price', 'num', 'total', 'memo', "wh_from", "wh_to", "from_position"]
    [COL_BILLID,COL_PEERID,COL_PROID,COL_PRONAME,COL_UNIT,COL_PRICE,
    COL_NUM,COL_TOTAL, COL_MEMO,COL_FROM, COL_TO, COL_FROM_POS] = [i for i in range(12)]
    fi_key = -1
    m_fieldDefVals = ['']*12
    b_basic_data = False
    
    def __init__(self):
        CDataModel.__init__(self)
        self.m_fieldVals = [''] * len(self.m_fieldNames)

    @classmethod
    def getProDetail(cls, billId):
        '''获取订单销售产品列表
            return - [[id,name,unit,cnt,price,total],[...],...]
        '''
        sql = "select proId,proName,unit,num,price,total,memo "\
                "from %s "\
                "where billId='%s'" %(cls.tbl, billId)
                
        cnt = dbmngr.dbmngr.execute(sql)
        if (cnt != 0): 
            return dbmngr.dbmngr.cursor.fetchall()
        else:
            return []

    @classmethod
    def GetPrintDetails(cls, billId):
        sql = """SELECT customer.name, proName, num, package
                FROM saledetails
                LEFT JOIN customer ON customer.id=peerId
                LEFT JOIN product ON proId=product.id
                WHERE billId="%s" AND notcountable = 0;""" %(billId)
                
        cnt = dbmngr.dbmngr.execute(sql)
        if (cnt != 0): 
            return dbmngr.dbmngr.cursor.fetchall()
        else:
            return []
        
    @classmethod
    def getCustomerExpense(cls, peerId, dateStart):
        data = []
        stat = cls.getStat(peerId)
        for proId,cnt,amount in stat:
            #如果有统计，则必有消费,[0]为最近订单信息
            # 150430 - 需要增加处理多单位的情况
            #proName,unit,price,billId,num,total = cls.getProSaleInfo(proId,peerId)[0]
            #data.append([billId,proId,proName,unit,price,cnt,amount])
            result = cls.getProSaleInfo(proId,peerId)
            maxBillId = result[0][3]
            for proName,unit,price,billId,num,total,state,memo,quantity,position in result:
                if billId == maxBillId and billId >= dateStart:
                    data.append([billId,proId,proName,unit,price,cnt,amount,quantity,position])
        
        return data    
        
    @classmethod
    def getStat(cls, peerId):
##        sql = "select distinct proId,proName,unit,price "\
##                "from %s " \
##                "where custId=%s order by saleId desc" %(cls.tbl, custId)
##        sql = "select first(proId),proName,unit,price "\
##                "from %s " \
##                "where custId=%s order by saleId desc" %(cls.tbl, custId)
        # 该SQL得到的price有问题，简单起见目前把pro销售统计与近期消费分开
        # 作为两条sql
##        sql = "select max(saleId),proId,proName,unit,price,sum(num),sum(total) "\
##                "from %s " \
##                "where custId=%s gruop by proId" %(cls.tbl, custId)

        sql = "select proId,sum(num),sum(total) "\
                "from %s " \
                "where peerId=%s group by proId" %(cls.tbl, peerId)
        cnt = dbmngr.dbmngr.execute(sql)
        print sql
        print 'cnt %d' %cnt
        if (cnt != 0): 
            return dbmngr.dbmngr.cursor.fetchall()
        else:
            return []
        
    @classmethod
    def getProSaleInfo(cls, proId, peerId=-1, regionId=-1, show_peihuo = False):
	
        filterStr = ' proId=%s ' %proId
        if(peerId != -1):
            resultStr = 'proName,unit,price,billId,num,saleDetails.total,state,saleDetails.memo,onhand1,from_position,dlvyInfo'
            filterStr = 'proId=%s and saleDetails.peerId=%s' %(proId, peerId)
        elif (regionId != -1):
            resultStr = 'proName,unit,price,billId,num,saleDetails.total,saleDetails.peerId,state,saleDetails.memo,onhand1,from_position,dlvyInfo'
            filterStr = 'proId=%s and region=%s' %(proId, regionId)
        else:
            resultStr = 'proName,unit,price,billId,num,saleDetails.total,saleDetails.peerId,state,saleDetails.memo,onhand1,from_position,dlvyInfo'
            filterStr = 'proId=%s' %proId
            
        sql = "select %s "\
                " from %s "\
                " left join product on proId=product.id " \
                " left join customer on saleDetails.peerId=customer.id" \
                " left join saleorder on saleorder.id=billId" \
                " where %s "\
                " order by billId desc"\
                " limit 30" %(resultStr, cls.tbl, filterStr, )    
         
        cnt = dbmngr.dbmngr.execute(sql)
        print sql
        print 'cnt %d' %cnt
        if (cnt != 0): 
            return dbmngr.dbmngr.cursor.fetchall()
        else:
            return []

    def incrInventory(self, incr=True):
        proId = self.getValue(self.COL_PROID)
        unit = self.getValue(self.COL_UNIT)
        num = self.getValue(self.COL_NUM)
        
        if not(incr):
            num = -num
        CDataProduct.updateOnhand(proId, unit, num)
    
    def decrInventory(self):
        self.incrInventory(False)
            
    def insert(self):
        self.incrInventory(False)
        return super(CDataSaleDetail, self).insert()
        
    def delete(self):
        self.incrInventory()
        return super(CDataSaleDetail, self).delete()
            
    @classmethod
    def delData(cls, fields):
        '''从数据库中删除数据，需要先修改相关产品库存
            fields - {COL_XX:xx,}
            return - itemCnt affected
        '''
        cls.adjustInventory(fields)
##        sql = 'delete from %s where ' %cls.tbl
##        sql += cls._convertSqlCondition(fields)
##            
##        return dbmngr.dbmngr.execute(sql)
    
        return super(CDataSaleDetail, cls).delete()

    
    @classmethod
    def adjustInventory(cls, fields, incr=True):
        sql = 'select proId,unit,num from %s where ' %cls.tbl
        sql += cls._convertSqlCondition(fields)
        
        cnt = dbmngr.dbmngr.execute(sql)
        result = dbmngr.dbmngr.cursor.fetchall()
        for proId,unit,num in result:
            if not(incr):
                num = -num
            CDataProduct.updateOnhand(proId, unit, num)

    @classmethod
    def getContent(cls, billId):
        sql = """SELECT proId,from_position,proName,spec,unit,num,price,total,d.memo,onhand1,peerId,lowest_qty
                FROM saledetails d LEFT JOIN product ON proId=product.id
                WHERE billId='%s'""" %(billId)
         
        cnt = dbmngr.dbmngr.execute(sql)
        print sql
        print 'cnt %d' %cnt
        if (cnt != 0): 
            return dbmngr.dbmngr.cursor.fetchall()
        else:
            return []

    @classmethod
    def GetPrintTagData(cls, billId):
        sql = """SELECT if(customer.type=14, saleorder.memo, if(trim(customer.printName) != "",customer.printName,customer.name))
                    , proName, num, unit, package
                FROM saledetails
                    LEFT JOIN customer ON peerId=customer.id
                    LEFT JOIN product ON proId=product.id
                    LEFT JOIN saleorder ON billId=saleorder.id
                WHERE billId='%s' and notcountable=0 and num>0 """ %(billId)
         
        cnt = dbmngr.dbmngr.execute(sql)
        print sql
        print 'cnt %d' %cnt
        if (cnt != 0): 
            return dbmngr.dbmngr.cursor.fetchall()
        else:
            return []

    @classmethod
    def ExportOrders(cls, idList):
        ''' 导出指掌天下需要的表格数据 
            录单时间 1
            交货时间
            订货客户 2 客户名称
            发货仓库 
            经手人 4
            部门
            结算单位
            单据备注 5
            抹零金额 6
            赠品
            商品编号 7
            商品条码
            商品名称 7A
            商品规格
            明细仓库
            单位 8
            数量 9
            副单位数量
            单价 10
            金额
            折扣 (%)
            折后单价
            折后金额
            明细备注 11
        '''
        sql = """SELECT date_format(billDate,'%%Y-%%m-%%d')
                    ,d.peerId
                    ,customer.name
                    ,staff.name
                    ,concat('[',saleorder.memo,']','[',saleorder.dlvyInfo,']','[',IFNULL(T.accountNames,''), IFNULL(T.paid,'0'),']','[',staff.name,']')
                    ,discount
                    ,proId
                    ,IFNULL(manualNum,proId)
                    ,proName
                    ,unit
                    ,num
                    ,price
                    ,d.memo
                FROM saledetails d 
                    LEFT JOIN saleorder ON billId=saleorder.id
                    left join customer on d.peerId=customer.id
                    left join staff on handlerId=staff.id
                    left join (select saleorder.id,SUM(voucher.money) as paid,GROUP_CONCAT(account.briefName) as accountNames
                        from saleorder,fund_customer,voucher,account
                        where saleorder.id=fund_customer.billId AND fund_customer.voucherId=voucher.id  and voucher.accountId=account.id AND saleorder.id in (%s)
                        GROUP BY saleorder.id) T on billId=T.id
                    left join product on d.proId=product.id
                WHERE billId in (%s)  and num > 0 order by billId desc""" %(idList, idList)
         
        cnt = dbmngr.dbmngr.execute(sql)
        print sql
        print 'cnt %d' %cnt
        if (cnt != 0): 
            return dbmngr.dbmngr.cursor.fetchall()
        else:
            return []


class CDataSaleDetailTemp(CDataSaleDetail):
    tbl = 'saleDetails_temp'
    m_dataList = []
    fi_key = -1
    b_basic_data = False
    
##    def insert(self):
##        #self.incrInventory(False)
##        return super(CDataSaleDetailTemp, self).insert()
##        
##    def delete(self):
##        #self.incrInventory()
##        return super(CDataSaleDetailTemp, self).delete()

class CDataOrderState(CDataModel):
    tbl = 'order_state'
    m_dataList = []
    m_fieldNo = {}
    m_fieldNames = []
    m_fieldCNNames = []
    m_fieldDefVals = []
    @classmethod
    def makeField(cls):
        '''系统启动时调用子类的该函数完成初始化'''
        #cls.m_fieldNames = []
        cls.fi_id = cls.appendField('id', u'编号')
        cls.fi_name = cls.appendField('name', u'状态名称')
        cls.refreshDataList()
    
    @classmethod
    def add(cls, name):
        if name == '':
            return
        cls.quickInsert({cls.fi_name:name})   
        
                
class CDataSaleOrder(CDataModel):
    tbl = 'saleOrder'
    m_dataList = []
    m_fieldNo = {}
    m_fieldNames = []
    m_fieldCNNames = []
    clsDetail = CDataSaleDetail
    listenerList = []
    
    m_fieldDefVals = []
    #m_fieldNames = ['id', 'peerId', 'billDate', 'total', 
    #                'discount', 'handlerId', 'recorderId', 'dlvyAddr', 'memo', 'buyer']
    #m_fieldCNNames = [u'编号', 'custId',  'billDate', 'total', 'paid',
    #                'unpaid', 'discount', 'handlerId', 'recorderId','dlvyId', 'memo', 'buyer']
    
    #[COL_ID,COL_PEERID,COL_BILLDATE,COL_TOTAL,COL_PAID,COL_UNPAID,
    #COL_DISCOUNT,COL_HANDLERID,COL_RECOREDERID,COL_DLVYADDR,COL_MEMO,COL_BUYER] = [i for i in range(12)]
    
    #clsDetail = CDataSaleDetail
    
##    def __init__(self):
##        CDataModel.__init__(self)
##        self.m_fieldVals = [''] * len(self.m_fieldNames)
    autoId = False
    prefix = 'XS'
    suffix = '00000'
    maxBillId = ''
    #记录已经申请的ID数量，避免同时实例化多个变量出现的ID重复申请
    #Note: 子类中应该重新声明该变量，以免被其他子类修改
    billIdCnt = 0
    b_basic_data = False
    
    @classmethod
    def newId(cls, date):
        maxid = max(CDataSaleOrder.newBillId(date))#, CDataSaleOrderTemp.newBillId(date))
        maxid = maxid[:-4]+'0'+maxid[-3:]
        return maxid
    
    @classmethod
    def makeField(cls):
        '''系统启动时调用子类的该函数完成初始化'''
        #cls.m_fieldNames = []
        cls.COL_ID = cls.appendField('id')
        cls.COL_PEERID = cls.appendField('peerId')
        cls.COL_BILLDATE = cls.appendField('billDate')
        #cls.COL_WHID = cls.appendField('warehouseId')
        cls.COL_TOTAL = cls.appendField('total')
        #cls.COL_PAID = cls.appendField('paid')
        cls.COL_DISCOUNT = cls.appendField('discount')
        cls.COL_HANDLERID = cls.appendField('handlerId')
        cls.COL_RECOREDERID = cls.appendField('recorderId')
        cls.COL_DLVYADDR = cls.appendField('dlvyAddr')
        cls.COL_MEMO = cls.appendField('memo')
        cls.COL_BUYER = cls.appendField('buyer')
        cls.COL_STATE = cls.appendField('state')
        cls.COL_DLVYID = cls.appendField('dlvyId', defVal=None)
        cls.COL_DELIVERYPLAN = cls.appendField('deliveryPlan', defVal=None)
        cls.COL_MODIFYTIME = cls.appendField('modifyTime', defVal=None)
        cls.COL_PRINTED = cls.appendField('printed', defVal=0)
        cls.COL_FROM = cls.appendField('wh_from', defVal=None)
        cls.COL_TO = cls.appendField('wh_to', defVal=None)
        cls.COL_DLVYINFO = cls.appendField('dlvyInfo', defVal="")

        #cls.refreshDataList()
                
        #cls.populateDataList()
        
        CRelFundCustomer.attach(cls.updateExtra)
    
    def isPrinted(self):
        return self.getValue(self.COL_PRINTED)
    
    def setPrinted(self):
        if (dbmngr.b_printer != 1):
            return

        self.setValue(self.COL_PRINTED, 1)
        #self.update()
        sql = "update saleorder set printed=1 where id='%s'"%(self.getValue(self.COL_ID))
        dbmngr.dbmngr.execute(sql)
    
    @classmethod
    def updateExtra(cls, op, fund):
        # 还会notify CDataSaleOrderTemp，不过没什么影响，暂时忽略
        #没必要立刻更新，只需要overDue，然后使用时更新即可
        orderId = fund.getValue(fund.COL_BILLID)
        print ('notify ', cls, op, orderId)
        obj = cls.findObj(orderId)
        if obj: # if for orderTemp
            obj.overDue = True
            obj.notify()
                
    # @classmethod
    # def populateDataList(cls):
    #     # 除数据库字段外，初始化时需要载入的数据（需要合成，以便UI可以快速反应）
    #     #index = len(cls.m_fieldNames)
    #     #cls.COL_PAID = index
    #     #cls.COL_UNPAID = index + 1
        
    #     for obj in cls.getObjList():
    #         obj.populateExtra()
    
    def populateExtra(self):
        self.paid = CRelFundCustomer.getPaidAmount(self.m_fieldVals[self.COL_ID])
        self.overDue = False
    
    def getPeerName(self):
        peerId = self.getValue(self.COL_PEERID)
        return CDataCustomer.getData({"id":peerId}).getValue(CDataCustomer.COL_NAME)
    
    @staticmethod
    def getPeerNameByBill(billid):
        sql = '''SELECT if(trim(customer.printName) != "",customer.printName,customer.name)
                FROM saleorder LEFT JOIN customer ON peerId=customer.id
                WHERE saleorder.id="%s"''' %(billid)
        cnt = dbmngr.dbmngr.execute(sql)
        if cnt == 0:
            return []
        
        ret =  dbmngr.dbmngr.cursor.fetchone()[0]
        return ret
        

    def getPeerObj(self):
        '''重构可以用字典优化效率'''
        peerId = self.getValue(self.COL_PEERID)
        return CDataCustomer.getData({"id":peerId})#CDataCustomer.findObj(peerId)
    
    def getDeliveryPlan(self):
        plan = self.getValue(self.COL_DELIVERYPLAN)
        if plan:
            return plan
        else:
            return u'' 
       
    def getPaid(self):
        if self.overDue:
            self.populateExtra()
        return self.paid
    
    def getUnpaid(self):
        total = self.m_fieldVals[self.COL_TOTAL]
        discount = self.m_fieldVals[self.COL_DISCOUNT]
        unpaid = total - discount - self.getPaid()
        return unpaid
    
    def getDlvyAddr(self):
        return self.getValue(self.COL_DLVYINFO) or ""
        # dlvyId = self.getValue(self.COL_DLVYID)
        # if (dlvyId is None):
        #     return u''
        
        # dlvyObj = CDataDelivery.getData({"id":dlvyId})#findObj(dlvyId)
        # return dlvyObj.getValue(dlvyObj.COL_NAME)

    def updateDlvyInfo(self, dlvyinfo):
        self.setValue(self.COL_DLVYINFO, dlvyinfo)
        sql = "update saleorder set dlvyInfo='%s' where id='%s'"%(dlvyinfo, self.getValue(self.COL_ID))
        dbmngr.dbmngr.execute(sql)
        
    @classmethod
    def getBillAmount(cls, peerId):
        '''
            return - [billId,amount,paid,discount,unpaid]
        '''
    
##        #sql = 'select id,total,paid,discount,unpaid '
##        sql = 'select id,total,discount '
##        sql += 'from %s where peerId=%d order by id desc' %(cls.tbl, peerId)
##                
##        cnt = dbmngr.dbmngr.execute(sql)
##        if (cnt > 0):
##            return dbmngr.dbmngr.cursor.fetchall()
##        else:
##            return []
        
        #list = cls.getObjs({cls.COL_PEERID:peerId})
        list = cls.fetch('peerId="%d"'%peerId)
        data = []
        for obj in list:
            data.append([obj.getValue(obj.COL_ID),
                        obj.getValue(obj.COL_TOTAL),
                        obj.getPaid(),
                        obj.getValue(obj.COL_DISCOUNT),
                        obj.getUnpaid()])
        return data
        
    @classmethod
    def getProDetail(cls, billId):
        '''return - [[id,name,unit,cnt,price,total],[...]] 与界面显示保持一致'''
        return cls.clsDetail.getProDetail(billId)
    
##    def delete(self):
##        return super(CDataSaleOrder, self).delete(True)
    
    @classmethod
    def deleteRefItem(cls, obj):
        
        # CRelFundCustomer
        id = obj.getValue(obj.COL_ID)
        CRelFundCustomer.quickDelete({CRelFundCustomer.COL_BILLID:id})
        
        # CDataSaleDetail
        CDataSaleDetail.quickDelete({CDataSaleDetail.COL_BILLID:id})

    @classmethod
    def clearDetail(cls, id):
        CDataSaleDetail.quickDelete({CDataSaleDetail.COL_BILLID:id})
    
    @classmethod
    def deleteBill(cls, billId, delFund=True):
        '''删除订单及订单明细'''
        # 添加收款单后，还需删除相关收款记录
        # 编辑订单时，如果id没有变化，则不需要解除收款关系
        if delFund:
            CRelFundCustomer.quickDelete({CRelFundCustomer.COL_BILLID:billId})
        CDataSaleDetail.quickDelete({CDataSaleDetail.COL_BILLID:billId})
        cls.quickDelete({cls.COL_ID:billId})        
        
    @classmethod
    def getCustomerDebt(cls, custId):
        sql = 'select sum(total),sum(paid),sum(unpaid),sum(discount) from %s where peerId=%s' %(cls.tbl,custId)
        cnt = dbmngr.dbmngr.execute(sql)
        print sql
        print 'cnt %d' %cnt
##        if (cnt != 0):
##            ret = dbmngr.dbmngr.cursor.fetchone()
##            print ret
##            return ret[0]
##        else:
##            return (0,0,0,0)
        #Maybe for sum() was used, there will be return for sure,
        #(None,None,None,None)will be returned if the custid unfound
        ret =  dbmngr.dbmngr.cursor.fetchone()
        if(ret is None):
            ret = (0.0,0.0,0.0,0.0)
        return ret
    
    @classmethod
    def getCustSaleAmount(cls, custId):
        '''获取应收总额（扣减优惠额之后的）'''
        sql = 'select sum(total),sum(discount) from %s where peerId=%s' %(cls.tbl,custId)
        cnt = dbmngr.dbmngr.execute(sql)
        #Maybe for sum() was used, there will be return for sure,
        #(None,None,None,None)will be returned if the custid unfound
        ret =  dbmngr.dbmngr.cursor.fetchone()
        if(ret[0] is None):
            ret = (0.0, 0.0)
        return ret[0] - ret[1]
    
##    def collect(self, money):
##        self.setValue(self.COL_PAID, self.getValue(self.COL_PAID) + money)
##        self.setValue(self.COL_UNPAID, self.getValue(self.COL_UNPAID) - money)
##        self.update()

    @classmethod
    def getStatByDay(cls, dateStart, dateEnd):
        sql = '''select billDate,sum(total),sum(discount), sum(total)-sum(discount),COUNT(DISTINCT peerId),COUNT(total>0 OR NULL)
                from %s
                where billDate between '%s' and '%s'
                group by billDate order by billDate desc
                ''' %(cls.tbl, dateStart, dateEnd)
        cnt = dbmngr.dbmngr.execute(sql)
        if cnt == 0:
            return []
        
        ret =  dbmngr.dbmngr.cursor.fetchall()
        return ret

    @classmethod
    def getMallStatByDay(cls, dateStart, dateEnd):
        '''商城日销售统计'''
        sql = '''SELECT DATE_FORMAT(billDate,"%%Y-%%m-%%d %%a")
                    ,sum(if(p.routeId=40, total-discount, 0)) as Tong
                    ,sum(if(p.routeId=41, total-discount, 0)) as Yi
                    ,sum(total-discount)
                    ,COUNT(DISTINCT peerId)
                    ,COUNT(total>0 OR NULL)
                    ,ROUND(sum(total-discount)/COUNT(DISTINCT peerId),2)
                FROM saleorder o
                    LEFT JOIN delivery_plan p ON o.deliveryPlan=p.Id 
                WHERE billDate between '%s' and '%s' AND (p.routeId=40 or p.routeId=41)
                group by billDate 
                order by billDate desc
                ''' %(dateStart, dateEnd)
        cnt = dbmngr.dbmngr.execute(sql)
        if cnt == 0:
            return []
        
        ret =  dbmngr.dbmngr.cursor.fetchall()
        return ret
    
    @classmethod
    def getStatByMonth(cls, year=0):
        
        filter = "where date_format(billDate, '%%Y')=%s"%year if year else ""
        sql = '''select date_format(billDate, '%%Y-%%m') as month,sum(total),sum(discount) , sum(total)-sum(discount)
                ,COUNT(DISTINCT peerId),COUNT(total>0 OR NULL)
                from %s 
                %s
                group by month order by month
                ''' %(cls.tbl, filter)
        cnt = dbmngr.dbmngr.execute(sql)
        if cnt == 0:
            return []
        
        ret =  dbmngr.dbmngr.cursor.fetchall()
        return ret

    @classmethod
    def getMallStatByMonth(cls, year=0):
        '''商城月统计报表'''
        
        #filter = "where date_format(billDate, '%%Y')=%s"%year if year else ""
        sql = '''SELECT date_format(billDate, '%Y-%m') as month
                    ,sum(total),sum(discount) , sum(total)-sum(discount)
                    ,COUNT(DISTINCT peerId)
                    ,COUNT(total>0 OR NULL)
                    ,sum(if(p.routeId=40, total, 0)) as Tong
                    ,sum(if(p.routeId=41, total, 0)) as Yi
                FROM saleorder o
                    LEFT JOIN delivery_plan p ON o.deliveryPlan=p.Id 
                WHERE p.routeId=40 or p.routeId=41
                GROUP BY month 
                ORDER BY month desc
                '''
        cnt = dbmngr.dbmngr.execute(sql)
        if cnt == 0:
            return []
        
        ret =  dbmngr.dbmngr.cursor.fetchall()
        return ret

    @classmethod
    def getProductStatByMonth(cls, proId):
        
        sql = '''SELECT date_format(billDate, '%%Y%%m') as month, sum(d.num), SUM(d.total)
                FROM saledetails d LEFT JOIN saleorder ON billId=id
                WHERE proId=%d
                GROUP BY month 
                order by month DESC
                ''' %(proId)
        cnt = dbmngr.dbmngr.execute(sql)
        if cnt == 0:
            return []
        
        ret =  dbmngr.dbmngr.cursor.fetchall()
        return ret

    @classmethod
    def getProductStatByRegion(cls, proId, billId):
        
        sql = '''SELECT customer_region.name, sum(d.num) AS qty, SUM(d.total)
                FROM saledetails d 
                    LEFT JOIN customer ON peerId=id
                    LEFT JOIN customer_region ON region=customer_region.Id
                WHERE proId=%d AND billId>'%s'
                GROUP BY region
                order by qty DESC
                ''' %(proId, billId)
        cnt = dbmngr.dbmngr.execute(sql)
        if cnt == 0:
            return []
        
        ret =  dbmngr.dbmngr.cursor.fetchall()
        return ret
    
    @classmethod
    def getStatByCustomer(cls, dateStart, dateEnd, showAll=False):
        
        # 这种统计连同没有销售的数据一起统计了，初衷是可以看到所有客户
        # 并且可以分辨出那些客户最近没有销售
        # 后来考虑，似乎没什么用处
        if showAll:
            sql = '''select name, sum(total), sum(discount), sum(money),COUNT(total>0 OR NULL),pioneer
                from (select customer.name ,sum(total) as total,sum(discount) as discount, sum(total)-sum(discount) as money
                        , staff.name as pioneer
                        from customer 
                            left join saleOrder on customer.id=saleOrder.peerId
                            left join staff on customer.pioneer=staff.id
                        where (billDate between '%s' and '%s')       
                        group by name
                    union
                        select customer.name, 0.0 as total, 0.0 as discount, 0.0
                            from customer ) t                     
                group by t.name'''%(dateStart, dateEnd)
        else:
            sql = '''select customer.name,sum(total) as total,sum(discount) as discount, sum(total)-sum(discount) as money,COUNT(total>0 OR NULL)
                        , staff.name as pioneer
                    from customer 
                        left join saleOrder on customer.id=saleOrder.peerId
                        left join staff on customer.pioneer=staff.id    
                    where (billDate between '%s' and '%s')       
                    group by name'''%(dateStart, dateEnd)
                    
        cnt = dbmngr.dbmngr.execute(sql)
        if cnt == 0:
            return []
        
        ret =  dbmngr.dbmngr.cursor.fetchall()
        return ret
    

    @classmethod
    def getMallStatByCustomer(cls, dateStart, dateEnd, showAll=False):
        
        sql = '''select name
                    ,sum(total) as total
                    ,sum(discount) as discount
                    ,sum(total)-sum(discount) as money
                    ,COUNT(total>0 OR NULL)
                    ,sum(if(p.routeId=40, total-discount, 0)) as Tong
                    ,sum(if(p.routeId=41, total-discount, 0)) as Yi
                from saleOrder o
                    left join customer c on c.id=o.peerId
                    LEFT JOIN delivery_plan p ON o.deliveryPlan=p.Id     
                where billDate between '%s' and '%s' AND (p.routeId=40 or p.routeId=41)
                group by name'''%(dateStart, dateEnd)
                    
        cnt = dbmngr.dbmngr.execute(sql)
        if cnt == 0:
            return []
        
        ret =  dbmngr.dbmngr.cursor.fetchall()
        return ret


    @classmethod
    def getStatByCustomerType(cls, dateStart, dateEnd):
        
        sql = '''select customer_type.name ,sum(total) as total,sum(discount) as discount, sum(total)-sum(discount),COUNT(DISTINCT peerId),COUNT(total>0 OR NULL)
                from saleOrder,customer,customer_type
                where billDate between '%s' and '%s'
                      and saleOrder.peerId=customer.id
                      and customer.type=customer_type.id
                group by customer_type.name'''%(dateStart, dateEnd)
        cnt = dbmngr.dbmngr.execute(sql)
        if cnt == 0:
            return []
        
        ret =  dbmngr.dbmngr.cursor.fetchall()
        return ret
    
    @classmethod
    def getStatByCustomerRegion(cls, dateStart, dateEnd):
        
        sql = '''select customer_region.name ,sum(total) as total,sum(discount) as discount, sum(total)-sum(discount),COUNT(DISTINCT peerId),COUNT(total>0 OR NULL)
                from saleOrder,customer,customer_region
                where billDate between '%s' and '%s'
                      and saleOrder.peerId=customer.id
                      and customer.region=customer_region.id
                group by customer_region.name'''%(dateStart, dateEnd)
        cnt = dbmngr.dbmngr.execute(sql)
        if cnt == 0:
            return []
        
        ret =  dbmngr.dbmngr.cursor.fetchall()
        return ret

    @classmethod
    def getMallStatByCustomerRegion(cls, dateStart, dateEnd):
        ''' 获取商城区域统计 '''
        
        sql = '''select customer_region.name 
                    ,sum(total) as total
                    ,sum(discount) as discount
                    ,sum(total)-sum(discount)
                    ,COUNT(DISTINCT peerId)
                    ,COUNT(total>0 OR NULL)
                    ,sum(if(p.routeId=40, total-discount, 0)) as Tong
                    ,sum(if(p.routeId=41, total-discount, 0)) as Yi
                from saleOrder o
                    left join customer c on c.id=o.peerId
                    LEFT JOIN delivery_plan p ON o.deliveryPlan=p.Id
                LEFT JOIN customer_region ON c.region=customer_region.id
                where billDate between '%s' and '%s' AND (p.routeId=40 or p.routeId=41)  
                group by customer_region.name'''%(dateStart, dateEnd)
        cnt = dbmngr.dbmngr.execute(sql)
        if cnt == 0:
            return []
        
        ret =  dbmngr.dbmngr.cursor.fetchall()
        return ret
    
    @classmethod
    def getStatByStaff(cls, dateStart, dateEnd):
        
        sql = '''select staff.name ,sum(total) as total,sum(discount) as discount, sum(total)-sum(discount),COUNT(DISTINCT peerId),COUNT(total>0 OR NULL)
                from saleOrder,staff
                where billDate between '%s' and '%s'
                      and saleOrder.handlerId=staff.id
                group by staff.name'''%(dateStart, dateEnd)
        cnt = dbmngr.dbmngr.execute(sql)
        if cnt == 0:
            return []
        
        ret =  dbmngr.dbmngr.cursor.fetchall()
        return ret

    @classmethod
    def getMallStatByStaff(cls, dateStart, dateEnd):
        ''' 销售员商城下单 '''
        sql = '''select staff.name 
                    ,sum(total) as total
                    ,sum(discount) as discount
                    ,sum(total)-sum(discount)
                    ,COUNT(DISTINCT peerId)
                    ,COUNT(total>0 OR NULL)
                    ,sum(if(p.routeId=40, total-discount, 0)) as Tong
                    ,sum(if(p.routeId=41, total-discount, 0)) as Yi
                from saleOrder o
                    LEFT JOIN staff ON o.handlerId=staff.id
                LEFT JOIN delivery_plan p ON o.deliveryPlan=p.Id
                where billDate between '%s' and '%s' AND (p.routeId=40 or p.routeId=41)  
                group by staff.name'''%(dateStart, dateEnd)
        cnt = dbmngr.dbmngr.execute(sql)
        if cnt == 0:
            return []
        
        ret =  dbmngr.dbmngr.cursor.fetchall()
        return ret
    
    @classmethod
    def getStatByDelivery(cls, dateStart, dateEnd):
        
        sql = '''select 'delivery',sum(total) as total,sum(discount) as discount, sum(total)-sum(discount),COUNT(DISTINCT peerId),COUNT(total>0 OR NULL)
                from saleOrder
                where billDate between '%s' and '%s'
                      and deliveryPlan is not Null
                union
                select 'other',sum(total) as total,sum(discount) as discount, sum(total)-sum(discount),COUNT(DISTINCT peerId),COUNT(total>0 OR NULL)
                from saleOrder
                where billDate between '%s' and '%s'
                      and deliveryPlan is Null  '''%(dateStart, dateEnd,dateStart, dateEnd)
        cnt = dbmngr.dbmngr.execute(sql)
        if cnt == 0:
            return []
        
        ret =  dbmngr.dbmngr.cursor.fetchall()
        return ret
        
    @classmethod
    def getStatByProduct(cls, dateStart, dateEnd):
        
        sql = '''select proName,spec,sum(saledetails.total) as amount,sum(num),ROUND(sum(saledetails.total)/sum(num),2),min(price),max(price),onhand1
                from saledetails 
                    LEFT JOIN product ON proId=product.id
                    LEFT JOIN saleorder ON billId=saleorder.id
                where billDate between '%s' and '%s'
                GROUP BY proId
                ORDER BY amount DESC''' %(dateStart, dateEnd)
        cnt = dbmngr.dbmngr.execute(sql)
        if cnt == 0:
            return []
        
        ret =  dbmngr.dbmngr.cursor.fetchall()
        return ret

    @classmethod
    def getMallStatByProduct(cls, dateStart, dateEnd):
        '''商城产品销售情况'''
        
        sql = '''select proName
                    ,spec
                    ,sum(d.total) as amount
                    ,sum(num)
                    ,ROUND(sum(d.total)/sum(num),2)
                    ,min(price)
                    ,max(price)
                    ,onhand1
                    ,sum(if(p.routeId=40, d.total, 0)) as Tong
                    ,sum(if(p.routeId=41, d.total, 0)) as Yi
                from saledetails d
                    LEFT JOIN product ON proId=product.id
                    LEFT JOIN saleorder o ON billId=o.id
					LEFT JOIN delivery_plan p ON o.deliveryPlan=p.Id
                where billDate between '%s' and '%s' AND (p.routeId=40 or p.routeId=41)
                GROUP BY proId
                ORDER BY amount DESC''' %(dateStart, dateEnd)
        cnt = dbmngr.dbmngr.execute(sql)
        if cnt == 0:
            return []
        
        ret =  dbmngr.dbmngr.cursor.fetchall()
        return ret

    @classmethod
    def getStatByProduct(cls, dateStart, dateEnd):
        '''产品销量统计'''
        
        sql = '''select proName,spec,sum(saledetails.total) as amount,sum(num),ROUND(sum(saledetails.total)/sum(num),2),min(price),max(price),onhand1
                from saledetails 
                    LEFT JOIN product ON proId=product.id
                    LEFT JOIN saleorder ON billId=saleorder.id
                where billDate between '%s' and '%s'
                GROUP BY proId
                ORDER BY amount DESC''' %(dateStart, dateEnd)
        cnt = dbmngr.dbmngr.execute(sql)
        if cnt == 0:
            return []
        
        ret =  dbmngr.dbmngr.cursor.fetchall()
        return ret

    @classmethod
    def getMallStatByCategory(cls, dateStart, dateEnd):
        '''商城分类销售统计'''
        
        sql = '''select category.name
                        ,sum(d.total) as amount
                        ,sum(num)
                        ,sum(if(p.routeId=40, d.total, 0)) as Tong
                        ,sum(if(p.routeId=41, d.total, 0)) as Yi
                from saledetails d
                    LEFT JOIN product ON proId=product.id
					LEFT JOIN category ON product.categoryId=category.id
                    LEFT JOIN saleorder o ON billId=o.id
					LEFT JOIN delivery_plan p ON o.deliveryPlan=p.Id
                where billDate between '%s' and '%s' AND (p.routeId=40 or p.routeId=41)
                GROUP BY category.id
                ORDER BY amount DESC''' %(dateStart, dateEnd)
        cnt = dbmngr.dbmngr.execute(sql)
        if cnt == 0:
            return []
        
        ret =  dbmngr.dbmngr.cursor.fetchall()
        return ret

    @classmethod
    def getStatByCategory(cls, dateStart, dateEnd):
        
        sql = '''select category.name,sum(saledetails.total) as amount,sum(num)
                from saledetails 
                    LEFT JOIN product ON proId=product.id
                    LEFT JOIN saleorder ON billId=saleorder.id
                    LEFT JOIN category ON product.categoryId=category.id
                where billDate between '%s' and '%s'
                GROUP BY category.id
                ORDER BY amount DESC''' %(dateStart, dateEnd)
        cnt = dbmngr.dbmngr.execute(sql)
        if cnt == 0:
            return []
        
        ret =  dbmngr.dbmngr.cursor.fetchall()
        return ret
    
    @classmethod
    def valid_total(cls):
        
        sql = '''select customer.name,
                        saleorder.id, 
                        saleorder.total, 
                        sum(saledetails.total) as total2, 
                        (saleorder.total-sum(saledetails.total)) as diff
                from saleorder,saledetails,customer
                where saleorder.id = saledetails.billId    
                    and  saleorder.peerId=  customer.id        
                group by saleorder.id'''
        
        cnt = dbmngr.dbmngr.execute(sql)
        if cnt == 0:
            return []
        
        ret =  dbmngr.dbmngr.cursor.fetchall()
        return ret
    
    @staticmethod
    def valid_paid():
        
        sql = '''select customer.name,
                        saleorder.id, 
                        saleorder.total, 
                        saleorder.discount, 
                        sum(fund_customer.money) as money2, 
                        (saleorder.total-saleorder.discount-sum(fund_customer.money)) as diff
                from saleorder,fund_customer,customer
                where saleorder.id = fund_customer.billId    
                    and  saleorder.peerId=customer.id        
                group by saleorder.id'''
                
        cnt = dbmngr.dbmngr.execute(sql)
        if cnt == 0:
            return []
        
        ret =  dbmngr.dbmngr.cursor.fetchall()
        return ret
    
    @staticmethod
    def GetDeliveryProducts(delivery_plan_id):
        sql = '''SELECT d.proId,p.position,c.`name`,proName,p.spec,d.unit,d.num,d.price,d.total,d.memo,p.memo,p.onhand1
                FROM saledetails d
                    LEFT JOIN saleorder o ON o.id=d.billId  
                    LEFT JOIN product p ON d.proId=p.id
                    LEFT JOIN customer c ON c.id=d.peerId
                WHERE o.deliveryPlan='%s'
                ORDER BY proName''' %(delivery_plan_id)
        cnt = dbmngr.dbmngr.execute(sql)
        if cnt == 0:
            return []
        
        ret =  dbmngr.dbmngr.cursor.fetchall()
        return ret

    @staticmethod
    def queryOrders(dateStart,dateEnd
        ,peerId=None
        ,pioneerId=None # 拓新员工
        ,only_unpaid=False # 未付款
        #,only_fahuo=False # 仅发货
        ,order_type='all'):

        filter = "1=1"
        if (order_type == "all"):
            pass
        elif (order_type == "tong"):
            # 仅同莱创
            filter = " (P.routeId=%d or P.routeId=%d) "%(CDataDeliveryRoute.ROUTE_TONG, CDataDeliveryRoute.ROUTE_TONG_RETURN)
        elif (order_type == "yi"):
            # 仅易天达
            filter = " (P.routeId=%d or P.routeId=%d) "%(CDataDeliveryRoute.ROUTE_YI, CDataDeliveryRoute.ROUTE_YI_RETURN)
        elif (order_type == "tongyi"):
            # 仅同莱创+易天达
            filter = " P.routeId in (%d,%d,%d,%d) "%(CDataDeliveryRoute.ROUTE_TONG
                , CDataDeliveryRoute.ROUTE_TONG_RETURN
                , CDataDeliveryRoute.ROUTE_YI
                , CDataDeliveryRoute.ROUTE_YI_RETURN)
        elif (order_type == "notongyi"):
            # 非同莱创，非易天达，即自提、发货、送货、定货等
            filter = " (O.deliveryPlan is NULL or P.routeId not in (%d,%d,%d,%d)) "%(CDataDeliveryRoute.ROUTE_TONG
                , CDataDeliveryRoute.ROUTE_TONG_RETURN
                , CDataDeliveryRoute.ROUTE_YI
                , CDataDeliveryRoute.ROUTE_YI_RETURN)
        elif (order_type == "nosend"):
            # 自提与发货
            filter = " O.deliveryPlan is NULL "


        sql = u'''SELECT * FROM (SELECT O.id as order_id
                    ,date_format(billDate,"%%Y-%%m-%%d")
                    ,C.`name` as cust_name
                    ,O.total
                    ,IFNULL(SUM(F.money),0)
                    ,O.discount
                    ,if(ABS(O.total-IFNULL(SUM(F.money),0)-O.discount)<0.01,0,O.total-IFNULL(SUM(F.money),0)-O.discount) as unpaid
                    ,O.state
                    ,dlvyInfo
                    ,O.memo
                    ,IF(P.state=3,date_format(P.send_date,"%%Y%%m%%d"),O.deliveryPlan)
                    ,IF(O.printed=1,'√','x')
                    ,C.wechat
                    ,S_SALE.`name` as saler
                    ,S_OP.`name` as operator
                    ,O.peerId
                FROM saleorder O
                    LEFT JOIN customer C ON C.id=O.peerId
                    LEFT JOIN fund_customer F ON F.billId=O.id
                    LEFT JOIN staff S_SALE ON S_SALE.id=O.handlerId
                    LEFT JOIN staff S_OP ON S_OP.id=O.recorderId
                    LEFT JOIN delivery_plan P ON O.deliveryPlan=P.Id
                WHERE billDate between '%s' AND '%s'
                    AND %s
                    AND %s
                    AND %s
                GROUP BY order_id) T
                WHERE %s
                ORDER BY order_id DESC''' %(dateStart, dateEnd
                , "1=1" if peerId is None else "peerId=%d"%peerId
                , "1=1" if pioneerId is None else "C.pioneer=%d"%pioneerId
                , filter#, "O.deliveryPlan is NULL" if only_fahuo else "1=1"
                , "abs(unpaid)>0.01" if only_unpaid else "1=1")

        cnt = dbmngr.dbmngr.execute(sql)
        if cnt == 0:
            return []
        
        ret =  dbmngr.dbmngr.cursor.fetchall()
        return ret

    def GetRouteId(self):
        sql = '''SELECT routeId 
                FROM saleorder 
                LEFT JOIN delivery_plan ON deliveryPlan=delivery_plan.Id
                WHERE saleorder.id='%s' AND (routeId=40 OR routeId=41)''' %(self.getKeyVal())
        cnt = dbmngr.dbmngr.execute(sql)
        if cnt == 0:
            return 0
        
        ret =  dbmngr.dbmngr.cursor.fetchall()
        return ret[0][0]


class CDataSaleOrderTemp(CDataSaleOrder):
    tbl = 'saleorder_temp'
    m_dataList = []
    m_fieldNo = {}
    m_fieldNames = []
    m_fieldCNNames = []
    prefix = 'XS'
    suffix = '00000'
    maxBillId = '' #子类类属性要显示声明，否则与父类同
    clsDetail = CDataSaleDetailTemp
    autoId = False
    b_basic_data = False
    
    @classmethod
    def audit(cls, billId):
        '''进行审核，需要修改账务、库存（by detail）'''
        print ('audit:', billId)
        #obj = cls.getFirstInstance({cls.COL_ID:billId})
        #super(CDataSaleOrder, obj).insert()
        sql = "insert into saleorder select * from saleorder_temp where id='%s'" %billId
        cnt = dbmngr.dbmngr.execute(sql)
        sql = "insert into saledetails select * from saledetails_temp where billId='%s'" %billId
        cnt = dbmngr.dbmngr.execute(sql)
        cls.deleteBill(billId)
        
        # 账务、库存
        saleObj = CDataSaleOrder.getFirstInstance({CDataSaleOrder.COL_ID:billId})
        saleObj.decrInventory()
        #saleObj.add        
    
    def deleteRefItem(self):
        #CDataSaleDetailTemp.delData({CDataSaleDetailTemp.COL_BILLID:self.getValue(self.COL_ID)})
        CDataSaleDetailTemp.quickDelete({CDataSaleDetailTemp.COL_BILLID:self.getValue(self.COL_ID)})
        
    @classmethod
    def deleteBill(cls, billId):
        '''删除订单及订单明细'''
        CDataSaleDetailTemp.quickDelete({CDataSaleDetailTemp.COL_BILLID:billId})
        cls.quickDelete({cls.COL_ID:billId}) 
            
class CDataDelivery(CDataModel):
    tbl = 'delivery'
    m_dataList = []
    m_fieldNo = {}
    m_fieldNames = ['id', 'name', 'memo']
    m_fieldCNNames = ['id', 'name', 'memo']
    m_fieldDefVals = [-1,'','']
    [COL_ID, COL_NAME, COL_MEMO] = [i for i in range(3)]
        
class CDataSupplier(CDataModel):
    tbl = u'supplier'
    m_dataList = []
    m_fieldNo = {}
    m_fieldNames = ['id','name','briefCode','addr','email',
                    'phone','fax','linkman','lm_phone',
                    'lm_memo','debt','memo']

    listenerList = []
    #m_fieldDefVals = [-1, '','','','','','','','','','', '', None, None]
    #[COL_ID,COL_NAME,COL_BREIFCODE,COL_ADDR, COL_EMAIL,COL_PHONE,COL_FAX,
    #COL_LINKMAN,COL_LMPHONE,COL_LMMEMO,COL_DEBT,COL_MEMO] = [i for i in range(12)]
    
    m_id = 'id'
    m_name = 'name'
    m_bCode = 'briefCode'
    m_addr = 'addr'
    m_email = 'email'
    m_phone = 'phone'
    m_fax = 'fax'
    m_linkman = 'linkman'
    m_lmPhone = 'lm_phone'
    m_lmMemo = 'lm_memo'
    m_InitDebt = 'debt'
    m_memo = 'memo'
    
    def __init__(self):    
        CDataModel.__init__(self)
        
        #self.m_fieldVals = [-1, '','','','','','','','','','', '', None, None]
    
    @classmethod
    def makeField(cls):
        '''系统启动时调用子类的该函数完成初始化'''
        #cls.m_fieldNames = []
        #cls.m_fieldDefVals = []
        cls.COL_ID = cls.appendField('id', defVal=-1)
        cls.COL_NAME = cls.appendField('name')
        cls.COL_BREIFCODE = cls.appendField('briefCode')
        cls.COL_ADDR = cls.appendField('addr')
        cls.COL_EMAIL = cls.appendField('email')
        cls.COL_PHONE = cls.appendField('phone')
        cls.COL_FAX = cls.appendField('fax')
        cls.COL_LINKMAN = cls.appendField('linkman')
        cls.COL_LMPHONE = cls.appendField('lm_phone')
        cls.COL_LMMEMO = cls.appendField('lm_memo')
        cls.COL_DEBT = cls.appendField('debt')
        cls.COL_MEMO = cls.appendField('memo')
        cls.COL_PRE_ARREARS = cls.appendField('pre_arrears', defVal=None)
        cls.COL_SETTLE_DATE = cls.appendField('settle_date', defVal=None)
        
        cls.refreshDataList()
        
        cls.listen()
    
    @classmethod    
    def listen(cls):
        CRelFundSupplier.attach(cls.updateExtraByFund)
        CDataPurOrder.attach(cls.updateExtraByOrder)
    
    @classmethod
    def getSearchSql(cls, val):
        str = "'%%%s%%'"  %val
        sql = ''
        # 'lastOrder'是Date类型，like报错1271
        # for field in CDataCustomer.m_fieldNames:
        #     if (field in ['lastOrder', 'last_contact', 'create_time']):
        #         continue
        #     sql += '%s like %s or ' %(field, str)
        for field in ['name','briefCode','addr','email','fax','linkman','lm_phone','lm_memo','memo']:
            sql += '%s like %s or ' %(field, str)
        return sql[:-4]

    @classmethod
    def updateExtraByFund(cls, op, fund):
        id = fund.getPeerId()
        print ('notify ', op, id)
        if id is not None:
            obj = cls.findObj(id)
            if obj is not None:
                obj.overDue = True
        
                obj.notify()
    
    @classmethod    
    def updateExtraByOrder(cls, op, order):
        id = order.getValue(order.COL_PEERID)
        print ('notify ', op, id)
        obj = cls.findObj(id)
        obj.overDue = True
        
        obj.notify()
                
    def getDebt(self):
        if self.overDue:
            #self.populateExtra()
            self.getDebtFromDB()
        return self.debt
    
    @classmethod
    def getName(cls, id):
        obj = cls.getData({"id":id})
        return obj.getValue(cls.COL_NAME) if obj is not None else ""
        
    def assignNewId(self):
        id = self.newAutoIncrId()
        self.setValue(self.COL_ID, id)
        return id
    
    def populateExtra(self):
        #获取欠款情况：应收货款总金额-已收货款总金额+期初欠款
        # 该数据的获取方式决定listen对象
        # self.debt = self.getPurTotal() - self.getPaidTotal() + self.getInitDebt()
        self.overDue = True
        pass

    def getDebtFromDB(self):
        #获取欠款情况：应收货款总金额-已收货款总金额+期初欠款
        #self.debt = self.getSaleAmount() - self.getPaidAmount() + self.m_fieldVals[self.COL_DEBT]
        sql = '''SELECT debt+IFNULL(sales,0)-IFNULL(paid,0) 
                FROM supplier
                    LEFT JOIN (SELECT peerId, SUM(total-discount) as sales from purorder WHERE peerId=%d) T1 ON supplier.id=T1.peerId
                    LEFT JOIN (SELECT peerId, SUM(money) as paid FROM voucher_supplier WHERE peerId=%d)  T2 ON supplier.id=T2.peerId
                WHERE supplier.id=%d''' %(self.getValue(self.COL_ID),self.getValue(self.COL_ID),self.getValue(self.COL_ID))
        # 240826 - 本来打算改成从对账归结日期 settle_date 开始计算，但是怕归结操作出错，还是看总账吧
        cnt = dbmngr.dbmngr.execute(sql)
        self.debt =  dbmngr.dbmngr.cursor.fetchone()[0]
        if abs(self.debt) < 0.01:
            self.debt = 0.0
        self.overDue = False        
    
    def getInitDebt(self):
        return self.m_fieldVals[self.COL_DEBT]
    
    def getPurTotal(self):
        peerId = self.getValue(self.COL_ID)
        return CDataPurOrder.getPurTotal(peerId)
    
    def getPaidTotal(self):
        peerId = self.getValue(self.COL_ID)
        return CDataVoucherSupplier.getPaid(peerId)
    
    @classmethod        
    def getProRecent(cls, supId):
        return CDataPurDetail.getProRecent(supId)
    
    @classmethod
    def GetLatestProList(cls, custId, filterSql=''):#categorys=None):

        sql = '''SELECT last_billid
                    ,d.proId
                    ,d.proName
                    ,p.spec
                    ,d.unit
                    ,d.price as latest_price
                    ,p.onhand1
                    ,p.position                    
                    ,sum_qty
                    ,sum_total
                    ,d.charge
                    ,d.memo
                    ,if(onUse=1,'O','X')
                    ,lowest_qty
                FROM purdetails d
                LEFT JOIN
                    (SELECT proId as id,SUM(total) as sum_total,SUM(num) as sum_qty,MAX(d.billId) as last_billid
                    FROM purdetails d
                        LEFT JOIN supplier c ON c.id=d.peerId
                    WHERE c.id=%d
                    GROUP BY proId) T ON T.id=d.proId 
                LEFT JOIN supplier c ON c.id=d.peerId
                LEFT JOIN product p ON p.id=d.proId
                WHERE c.id=%d AND last_billid=d.billId %s
                GROUP BY d.proId
                ORDER BY d.proName DESC''' %(custId, custId
                        , " and %s " %(filterSql) if (len(filterSql)>0) else "")
                        #, " categoryId in (%s)"%(",".join(str(e) for e in categorys)) if (categorys is not None and len(categorys)>0) else "") 
        print sql 
        cnt = dbmngr.dbmngr.execute(sql)
        print 'cnt %d' %cnt
        if (cnt != 0): 
            return dbmngr.dbmngr.cursor.fetchall()
        else:
            return []

    def UpdateSettleInfo(self):
        sql = '''update supplier set pre_arrears=%f,settle_date='%s' where id=%d'''%(self.getValue(self.COL_PRE_ARREARS),
            self.getValue(self.COL_SETTLE_DATE), self.getValue(self.COL_ID))

        dbmngr.dbmngr.execute(sql)

    def getCheckProDetails(self, check_all=True):
        ''' 对账读取商品明细 '''
        if (check_all or self.getValue(self.COL_SETTLE_DATE) is None):
            sql = """SET @running_total := (SELECT debt FROM supplier WHERE id=%d);""" %(self.getValue(self.COL_ID))
            sql_settle = ''
            sql_voucher = ''
        else:
            sql = """SET @running_total := (SELECT pre_arrears FROM supplier WHERE id=%d);""" %(self.getValue(self.COL_ID))
            sql_settle = ' AND billDate>"%s" ' %(str(self.getValue(self.COL_SETTLE_DATE)))
            sql_voucher = ' AND date>"%s" ' %(str(self.getValue(self.COL_SETTLE_DATE)))

        dbmngr.dbmngr.execute(sql)

        # sql = """SELECT billId
        #             ,proName
        #             ,spec
        #             ,unit
        #             ,num
        #             ,price
        #             ,purdetails.total
        #             ,@running_total := @running_total + purdetails.total AS balance
        #             ,purdetails.memo
        #         FROM purdetails
        #             LEFT JOIN product ON purdetails.proId=product.id
        #             LEFT JOIN purorder ON purdetails.billId=purorder.id
        #         WHERE purdetails.peerId = %d %s
        #         ORDER BY billId ASC;""" %(self.getValue(self.COL_ID)
        #         ,sql_settle)
        # 240830 加入收款单
        sql = """SELECT billId
                        ,billDate
                        ,proName
                        ,spec
                        ,unit
                        ,num
                        ,price
                        ,ROUND(total,2) as total
                        ,@running_total := @running_total + total AS balance
                        ,memo
                        ,bill_type
                FROM (SELECT billId
                        ,billDate
                        ,proName
                        ,spec
                        ,unit
                        ,num
                        ,price
                        ,ROUND(purdetails.total,2) as total
                        ,purdetails.memo
                        ,0 as bill_type
                    FROM purdetails
                        LEFT JOIN product ON purdetails.proId=product.id
                        LEFT JOIN purorder ON purdetails.billId=purorder.id
                    WHERE purdetails.peerId = %d %s

                    UNION

                    SELECT voucher_supplier.id
                        ,date
                        ,account.briefName
                        ,'**********'
                        ,''
                        ,''
                        ,''
                        ,-money
                        ,''
                        ,1 as bill_type
                    FROM voucher_supplier LEFT JOIN account ON accountId=account.id
                    WHERE voucher_supplier.peerId = %d %s) T

                ORDER BY billDate ASC,bill_type ASC  ;
        """%(self.getValue(self.COL_ID)
            ,sql_settle
            ,self.getValue(self.COL_ID)
            ,sql_voucher)

        print (sql)
        cnt = dbmngr.dbmngr.execute(sql)        
        print ('cnt %d' %cnt)
        if (cnt != 0): 
            return dbmngr.dbmngr.cursor.fetchall()
        else:
            return []
    
class CDataPurOrder(CDataModel):
    tbl = 'purOrder'
    m_dataList = []
    m_fieldNo = {}
    #m_fieldNames = ['id', 'peerId', 'billDate', 'warehouseId','total', 'paid',#'unpaid', 
    #                'discount', 'handlerId', 'recorderId', 'memo']
    m_fieldCNNames = []
    m_fieldDefVals = []
    #[COL_ID,COL_PEERID,COL_BILLDATE,COL_WHID,COL_TOTAL,COL_PAID,#COL_UNPAID,
    #COL_DISCOUNT,COL_HANDLERID,COL_RECOREDERID,COL_MEMO] = [i for i in range(10)]
    
    listenerList = []
    autoId = False
    
    prefix = 'CJ'
    suffix = '00000'
    billIdCnt = 0 # 避免同时建立多个新订单时id重复
    b_basic_data = False
    
#    def __init__(self):
#        CDataModel.__init__(self)
#        self.m_fieldVals = [''] * len(self.m_fieldNames)
    
    @classmethod
    def makeField(cls):
        '''系统启动时调用子类的该函数完成初始化'''
        #cls.m_fieldNames = []
        cls.COL_ID = cls.appendField('id')
        cls.COL_PEERID = cls.appendField('peerId')
        cls.COL_BILLDATE = cls.appendField('billDate')
        cls.COL_WHID = cls.appendField('warehouseId')
        cls.COL_TOTAL = cls.appendField('total')
        #cls.COL_PAID = cls.appendField('paid')
        cls.COL_DISCOUNT = cls.appendField('discount')
        cls.COL_HANDLERID = cls.appendField('handlerId')
        cls.COL_RECOREDERID = cls.appendField('recorderId')
        cls.COL_MEMO = cls.appendField('memo')
        cls.COL_WH_IN = cls.appendField('wh_in', defVal=None)
        
        #cls.refreshDataList()
                
        #cls.populateDataList()
        
        CRelFundSupplier.attach(cls.updatePaid)
    
    @classmethod
    def updatePaid(cls, op, fund):
        # 还会notify CDataSaleOrderTemp，不过没什么影响，暂时忽略
        orderId = fund.getValue(fund.COL_BILLID)
        print ('notify ', op, orderId)
        obj = cls.findObj(orderId)
        if obj:
            obj.overDue = True
            obj.notify()
    
    # @classmethod
    # def populateDataList(cls):
    #     # 除数据库字段外，初始化时需要载入的数据（需要合成，以便UI可以快速反应）
    #     #index = len(cls.m_fieldNames)
    #     #cls.COL_PAID = index
    #     #cls.COL_UNPAID = index + 1
        
    #     for obj in cls.getObjList():
    #         obj.populateExtra()
    
    def populateExtra(self):
        self.paid = CRelFundSupplier.getPaidAmount(self.m_fieldVals[self.COL_ID])
        self.overDue = False
        
    def getPaid(self):
        if self.overDue:
            self.populateExtra()
        return self.paid
    
    def getUnpaid(self):
        total = self.m_fieldVals[self.COL_TOTAL]
        discount = self.m_fieldVals[self.COL_DISCOUNT]
        return total - discount - self.getPaid()
        
    @classmethod        
    def deleteRefItem(cls, obj): 
        # CRelFundCustomer
        id = obj.getValue(obj.COL_ID)
        CRelFundSupplier.quickDelete({CRelFundSupplier.COL_BILLID:id})
        
        # CDataSaleDetail
        CDataPurDetail.quickDelete({CDataPurDetail.COL_BILLID:id})

    @classmethod
    def clearDetail(cls, id):
        CDataPurDetail.quickDelete({CDataPurDetail.COL_BILLID:id})
            
    @classmethod
    def deleteBill(cls, billId, delFund=True):
        '''删除订单及订单明细'''
        # 添加收款单后，还需删除相关收款记录
        # 编辑订单时，如果id没有变化，则不需要解除收款关系
        # 150420 - 实际上，编辑订单时应该用update，现在用的是del/insert才这么多麻烦
        if delFund:
            CRelFundSupplier.quickDelete({CRelFundSupplier.COL_BILLID:billId})

        CDataPurDetail.quickDelete({CDataPurDetail.COL_BILLID:billId})
        cls.quickDelete({cls.COL_ID:billId})
    
    @classmethod
    def getBillAmount(cls, peerId):
        '''
            return - [billId,amount,paid,discount,unpaid]
        '''
    
##        sql = 'select id,total,discount '
##        sql += 'from %s where peerId=%d order by id desc' %(cls.tbl, peerId)
##                
##        cnt = dbmngr.dbmngr.execute(sql)
##        if (cnt > 0):
##            return dbmngr.dbmngr.cursor.fetchall()
##        else:
##            return []
        
        #list = cls.getObjs({cls.COL_PEERID:peerId})
        list = cls.fetch('peerId=%d'%peerId)
        data = []
        for obj in list:
            data.append([obj.getValue(obj.COL_ID),
                        obj.getValue(obj.COL_TOTAL),
                        obj.getPaid(),
                        obj.getValue(obj.COL_DISCOUNT),
                        obj.getUnpaid()])
        return data
        
    @classmethod
    def getProDetail(cls, billId):
        '''return - [[id,name,unit,cnt,price,total],[...]] 与界面显示保持一致'''
        return CDataPurDetail.getProDetail(billId)     
    
    @classmethod
    def getPurTotal(cls, peerId):
        '''获取应收总额（扣减优惠额之后的）'''
        sql = 'select sum(total),sum(discount) from %s where peerId=%s' %(cls.tbl,peerId)
        cnt = dbmngr.dbmngr.execute(sql)
        #Maybe for sum() was used, there will be return for sure,
        #(None,None,None,None)will be returned if the custid unfound
        ret =  dbmngr.dbmngr.cursor.fetchone()
        if(ret[0] is None):
            ret = (0.0, 0.0)
        return ret[0] - ret[1]

    @classmethod
    def valid_total(cls):
        
        sql = '''select supplier.name,
                        purorder.id, 
                        purorder.total, 
                        sum(purdetails.total) as total2, 
                        (purorder.total-sum(purdetails.total)) as diff
                from purorder,purdetails,supplier
                where purorder.id = purdetails.billId    
                    and  purorder.peerId=  supplier.id        
                group by purorder.id'''
        
        cnt = dbmngr.dbmngr.execute(sql)
        if cnt == 0:
            return []
        
        ret =  dbmngr.dbmngr.cursor.fetchall()
        return ret
    
    @staticmethod
    def valid_paid():
        
        sql = '''select supplier.name,
                        purorder.id, 
                        purorder.total,
                        purorder.discount,
                        sum(fund_supplier.money) as money2, 
                        (purorder.total-purorder.discount-sum(fund_supplier.money)) as diff
                from purorder,fund_supplier,supplier
                where purorder.id = fund_supplier.billId    
                    and  purorder.peerId=  supplier.id        
                group by purorder.id'''
                
        cnt = dbmngr.dbmngr.execute(sql)
        if cnt == 0:
            return []
        
        ret =  dbmngr.dbmngr.cursor.fetchall()
        return ret

    @classmethod
    def getStat(cls, dateStart, dateEnd, showAll=False):
        
        # 这种统计连同没有销售的数据一起统计了，初衷是可以看到所有客户
        # 并且可以分辨出那些客户最近没有销售
        # 后来考虑，似乎没什么用处
        if showAll:
            sql = '''select name, sum(total), sum(discount), sum(money),COUNT(total>0 OR NULL)
                from (select name ,sum(total) as total,sum(discount) as discount, sum(total)-sum(discount) as money
                        from supplier left join purorder 
                                    on supplier.id=purorder.peerId    
                        where (billDate between '%s' and '%s')       
                        group by name
                    union
                        select supplier.name, 0.0 as total, 0.0 as discount, 0.0
                            from supplier ) t                     
                group by t.name'''%(dateStart, dateEnd)
        else:
            sql = '''select name,sum(total) as total,sum(discount) as discount, sum(total)-sum(discount) as money,COUNT(total>0 OR NULL)
                        from supplier left join purorder 
                                    on supplier.id=purorder.peerId    
                        where (billDate between '%s' and '%s')       
                    group by name'''%(dateStart, dateEnd)
                    
        cnt = dbmngr.dbmngr.execute(sql)
        if cnt == 0:
            return []
        
        ret =  dbmngr.dbmngr.cursor.fetchall()
        return ret


class CDataPurDetail(CDataModel):
    tbl = 'purDetails'
    m_dataList = []
    m_fieldNo = {}
    m_fieldNames = ['billId', 'peerId','proId', 'proName', 'unit','price', 'num', 'total', 'memo', 'charge', 'wh_in']
    #m_fieldCNNames = ['saleId', 'custId', 'proId',,'unit', 'price', 'num', 'total']
    m_fieldDefVals = [''] * 11
    [COL_BILLID,COL_PEERID,COL_PROID,COL_PRONAME,COL_UNIT,COL_PRICE,
    COL_NUM,COL_TOTAL, COL_MEMO, COL_CHARGE, COL_WH_IN] = [i for i in range(11)]
    fi_key = -1
    b_basic_data = False
                    
##    def __init__(self):
##        CDataModel.__init__(self)
##        self.m_fieldVals = [''] * len(self.m_fieldNames)
    
    def incrInventory(self, incr=True):
        proId = self.getValue(self.COL_PROID)
        unit = self.getValue(self.COL_UNIT)
        num = self.getValue(self.COL_NUM)
        
        if not(incr):
            num = -num
        CDataProduct.updateOnhand(proId, unit, num)

    def insert(self):
        # update pro info
        proId = self.getValue(self.COL_PROID)
        unit = self.getValue(self.COL_UNIT)
        price = self.getValue(self.COL_PRICE)
        num = self.getValue(self.COL_NUM)
        CDataProduct.updatePurchase(int(proId), unit, float(price), float(num))
        
        # insert self
        return super(CDataPurDetail, self).insert()
    
    def delete(self):
        self.incrInventory(False)
        return super(CDataPurDetail, self).delete()
    
    @classmethod
    def adjustInventory(cls, fields, incr=True):
        sql = 'select proId,unit,num from %s where ' %cls.tbl
        sql += cls._convertSqlCondition(fields)
        
        cnt = dbmngr.dbmngr.execute(sql)
        result = dbmngr.dbmngr.cursor.fetchall()
        for proId,unit,num in result:
            if not(incr):
                num = -num
            CDataProduct.updateOnhand(proId, unit, num)
            
    @classmethod
    def delData(cls, fields=None):
        cls.adjustInventory(fields, False)
        sql = 'delete from %s where ' %cls.tbl
        sql += cls._convertSqlCondition(fields)
            
        return dbmngr.dbmngr.execute(sql)
                
    @classmethod
    def getProDetail(cls, billId):
        sql = "select proId,proName,unit,num,price,total,memo,charge "\
                "from %s "\
                "where billId='%s'" %(cls.tbl, billId)
                
        cnt = dbmngr.dbmngr.execute(sql)
        if (cnt != 0): 
            return dbmngr.dbmngr.cursor.fetchall()
        else:
            return []
        
    @classmethod
    def getProRecent(cls, peerId):
        data = []
        stat = cls.getStat(peerId)
        for proId,cnt,amount in stat:
            #如果有统计，则必有消费,[0]为最近订单信息
            proName,unit,price,billId,num,total,memo,quantity,position,charge,wh_in = cls.getProSaleInfo(proId,peerId)[0]
            data.append([billId,proId,proName,unit,price,cnt,amount,quantity,position,charge,memo])
        
        return data    
        
    @classmethod
    def getStat(cls, peerId):
        sql = "select proId,sum(num),sum(total) "\
                "from %s " \
                "where peerId=%s group by proId" %(cls.tbl, peerId)
        cnt = dbmngr.dbmngr.execute(sql)
        print sql
        print 'cnt %d' %cnt
        if (cnt != 0): 
            return dbmngr.dbmngr.cursor.fetchall()
        else:
            return []
        
    @classmethod
    def getProSaleInfo(cls, proId, peerId=-1):
        if(peerId != -1):
            resultStr = 'proName,unit,price,date_format(billDate, "%Y%m%d"),num,d.total,d.memo,onhand1,"",charge,w.name'
            filterStr = 'proId=%s and d.peerId=%s' %(proId, peerId)
        else:
            resultStr = 'proName,unit,price,date_format(billDate, "%Y%m%d"),num,d.total,d.peerId,d.memo,onhand1,"",charge,w.name'
            filterStr = 'proId=%s' %proId
            
        sql = "select %s "\
                " from %s d"\
                " left join product on proId=product.id " \
                " left join purorder on purorder.id=billId "\
                " left join warehouse w on d.wh_in=w.id "\
                " where %s order by billId desc limit 30" %(resultStr, cls.tbl, filterStr)    
         
        cnt = dbmngr.dbmngr.execute(sql)
        print sql
        print 'cnt %d' %cnt
        if (cnt != 0): 
            return dbmngr.dbmngr.cursor.fetchall()
        else:
            return []

    @classmethod
    def getContent(cls, billId):
        sql = """SELECT proId,position,proName,spec,unit,num,price,total,d.memo,charge,charge*num,weight,weight*num,onhand1,lowest_qty
                FROM %s d LEFT JOIN product ON proId=product.id
                WHERE billId='%s'""" %(cls.tbl, billId)
         
        cnt = dbmngr.dbmngr.execute(sql)
        print sql
        print 'cnt %d' %cnt
        if (cnt != 0): 
            return dbmngr.dbmngr.cursor.fetchall()
        else:
            return []

    @classmethod
    def ExportOrders(cls, idList):
        '''导出指掌格式订单 采购入库单
            录单时间	
            供应商	
            入库仓库	
            经手人	
            部门	
            单据备注	
            抹零金额	
            赠品	
            商品编号	
            商品条码	
            商品名称	
            商品规格	
            明细仓库	
            单位	
            数量	
            副单位数量	
            单价	
            金额	
            折扣 (%)	
            折后单价	
            折后金额	
            明细备注
        '''
        sql = """SELECT date_format(billDate,'%%Y-%%m-%%d')
                    ,concat('G',d.peerId)
                    ,supplier.name
                    ,staff.name
                    ,,concat('[',purorder.memo,']','[',IFNULL(T.accountNames,''), IFNULL(T.paid,'0'),']')
                    ,discount
                    ,proId
                    ,IFNULL(manualNum,proId)
                    ,proName
                    ,unit
                    ,num
                    ,price
                    ,d.memo
                FROM purdetails d 
                    LEFT JOIN purorder ON billId=purorder.id
                    left join supplier on d.peerId=supplier.id
                    left join staff on handlerId=staff.id
                    left join (select purorder.id,SUM(voucher_supplier.money) as paid,GROUP_CONCAT(account.briefName) as accountNames
                        from purorder,fund_supplier,voucher_supplier,account
                        where purorder.id=fund_supplier.billId 
                            AND fund_supplier.voucherId=voucher_supplier.id  
                            AND voucher_supplier.accountId=account.id 
                            AND purorder.id in (%s)
                        GROUP BY purorder.id) T on billId=T.id
                    left join product on d.proId=product.id
                WHERE billId in (%s) and num > 0 order by billId desc""" %(idList,idList)
        
        cnt = dbmngr.dbmngr.execute(sql)
        print sql
        print 'cnt %d' %cnt
        if (cnt != 0): 
            return dbmngr.dbmngr.cursor.fetchall()
        else:
            return []


class CDataPeer(CDataModel):
    tbl = 'peer'
    m_dataList = []
    m_fieldDefVals = []
    
    @classmethod
    def makeField(cls):
        '''系统启动时调用子类的该函数完成初始化'''
        #cls.m_fieldNames = []
        cls.fi_id = cls.appendField('id')
        cls.fi_name = cls.appendField('name')
        cls.fi_briefCode = cls.appendField('briefCode')
        cls.fi_addr = cls.appendField('addr')
        cls.fi_email = cls.appendField('email')
        cls.fi_phone = cls.appendField('phone')
        cls.fi_fax = cls.appendField('fax')
        cls.fi_memo = cls.appendField('memo')
        cls.fi_linkman = cls.appendField('linkman')
        cls.fi_lmPhone = cls.appendField('lm_phone')
        cls.fi_lmMemo = cls.appendField('lm_memo')
        cls.fi_debt = cls.appendField('debt')
        
        cls.refreshDataList()
        
    def getDebt(self):
        id = self.getKeyVal()
        return CDataPeerFund.getAmount(id)+CDataPeerLend.getAmount(id)
        
class CDataPeerFund(CDataModel):
    tbl = 'peer_fund'
    m_dataList = []
    m_fieldDefVals = []
    b_basic_data = False
    
    @classmethod
    def makeField(cls):
        '''系统启动时调用子类的该函数完成初始化'''
        #cls.m_fieldNames = []
        cls.fi_id = cls.appendField('id')
        cls.fi_peerId = cls.appendField('peerId')
        cls.fi_account = cls.appendField('account')
        cls.fi_money = cls.appendField('money')
        cls.fi_date = cls.appendField('date')
        cls.fi_handler = cls.appendField('handler')
        cls.fi_recorder = cls.appendField('recorder')
        cls.fi_memo = cls.appendField('memo')
        
        cls.refreshDataList()
    
    @classmethod
    def getAmount(cls, peerId):
        sql = 'select sum(money) from %s where peerId=%d' %(cls.tbl, peerId)
        cnt = dbmngr.dbmngr.execute(sql)
        #Maybe for sum() was used, there will be return for sure,
        #(None)will be returned if the custid unfound
        result =  dbmngr.dbmngr.cursor.fetchone()
        if(result[0] is not None):
            return result[0]
        else:
            return 0.0
    
    def getAccountName(self):
        obj = CDataAccount.getData({"id":self.getValue(self.iAccountId)})#.findObj(self.getValue(self.fi_account))
        return obj.getValue(obj.COL_BRIEFNAME)
                                
class CDataPeerLend(CDataModel):
    tbl = 'peer_lend'
    m_dataList = []
    m_fieldDefVals = []
    b_basic_data = False
    
    @classmethod
    def makeField(cls):
        '''系统启动时调用子类的该函数完成初始化'''
        #cls.m_fieldNames = []
        cls.fi_id = cls.appendField('id')
        cls.fi_peerId = cls.appendField('peerId')
        cls.fi_date = cls.appendField('date')
        cls.fi_proId = cls.appendField('proId')
        cls.fi_proName = cls.appendField('proName')
        cls.fi_unit = cls.appendField('unit')
        cls.fi_num = cls.appendField('num')
        cls.fi_price = cls.appendField('price')
        cls.fi_total = cls.appendField('total')
        cls.fi_handler = cls.appendField('handler')
        cls.fi_recorder = cls.appendField('recorder')
        cls.fi_memo = cls.appendField('memo')
        
        cls.refreshDataList()
    
    @classmethod
    def getAmount(cls, peerId):
        sql = 'select sum(total) from %s where peerId=%d' %(cls.tbl, peerId)
        cnt = dbmngr.dbmngr.execute(sql)
        #Maybe for sum() was used, there will be return for sure,
        #(None)will be returned if the custid unfound
        result =  dbmngr.dbmngr.cursor.fetchone()
        if(result[0] is not None):
            return result[0]
        else:
            return 0.0

        
                                    
class CDataBackup(CDataModel):
    tbl = 'backup'
    m_dataList = []
    m_fieldNo = {}
    m_fieldNames = ['id', 'backupQuit', 'path', 'code','autoEmail','email','psw','lastFile']
    m_fieldDefVals = ['']*8
    
    [COL_ID,COL_BACKUPQUIT,COL_PATH,COL_CODE,COL_AUTOEMAIL,COL_EMAIL,
    COL_PSW,COL_LASTFILE] = [i for i in range(8)]

    @classmethod
    def getBackupConfig(cls):
        return cls.m_dataList[0]

    @classmethod
    def isBackupQuit(cls):
        return cls.m_dataList[0].m_fieldVals[cls.COL_BACKUPQUIT]
     
    @classmethod
    def updateLastFile(cls, file):
        file = file.replace('\\', '/')
        cls.m_dataList[0].m_fieldVals[cls.COL_LASTFILE] = file
        cls.m_dataList[0].update()

class CDataVersion(CDataModel):
    tbl = 'version'
    m_dataList = []
    m_fieldNo = {}
    m_fieldDefVals = []
    #m_fieldNames = ['id', 'version']
    
    @classmethod
    def makeField(cls):
        '''系统启动时调用子类的该函数完成初始化'''
        #cls.m_fieldNames = []
        cls.iId = cls.appendField('id')
        cls.iVersion = cls.appendField('version')
        cls.iInfo = cls.appendField('info')
        
        cls.refreshDataList()
    
    @classmethod
    def getVersion(cls):
        if not(cls.m_dataList):
            return '0.0'
        
        return cls.m_dataList[0].m_fieldVals[cls.iVersion]
        
class CDataVoucher(CDataModel):
    tbl = 'voucher'
    m_dataList = []
    m_fieldNo = {}
    m_fieldNames = []
    m_fieldDefVals = []
    listenerList = []
    b_basic_data = False
    
##    m_fieldNames = ['id', 'peerId', 'accountId', 'money','date','handlerId',
##                    'recorderId','memo']
    autoId = False
    prefix = 'KF'
    suffix = '0000'
    [COL_ID,COL_PEERID,COL_ACCOUNTID,COL_MONEY,COL_DATE,COL_HANDLERID,
    COL_RECORDERID,COL_MEMO,COL_CHECK_DATE] = [i for i in range(9)]

    # def __init__(self):
        # CDataModel.__init__(self)
        # self.m_fieldVals = [''] * len(self.m_fieldNames)
    
    @classmethod
    def makeField(cls):
        '''系统启动时调用子类的该函数完成初始化'''
        #cls.m_fieldNames = []
        cls.iId = cls.appendField('id')
        cls.iPeerId = cls.appendField('peerId')
        cls.iAccountId = cls.appendField('accountId')
        cls.iMoney = cls.appendField('money')
        cls.iDate = cls.appendField('date')
        cls.iHandlerId = cls.appendField('handlerId')
        cls.iRecorderId = cls.appendField('recorderId')
        cls.iMemo = cls.appendField('memo')
        cls.iCheckDate = cls.appendField('check_date')
        
        #cls.refreshDataList()
    
    def getAccountDetail(self):
        return CDataAccountDetail.getObjs({CDataAccountDetail.COL_FUNDID:self.getKeyVal()})

    
    def getSaleId(self):
        ret = CRelFundCustomer.getBillId(self.getValue(CDataVoucher.COL_ID))
        if not(ret):
            return ret[0]
        else:
            return ''
    
    def getBillId(self):
        '''获取关联的订单id列表'''
        return CRelFundCustomer.getBillId(self.getValue(self.COL_ID))
    
    def getAllocateInfo(self):
        return CRelFundCustomer.getAllocateInfo(self.getValue(self.COL_ID))
        
    @classmethod
    def getCustPaid(cls, custId):
        ret = 0
        sql = 'select sum(money) from %s where peerId=%s' %(cls.tbl,custId)
        cnt = dbmngr.dbmngr.execute(sql)
        #Maybe for sum() was used, there will be return for sure,
        #(None)will be returned if the custid unfound
        result =  dbmngr.dbmngr.cursor.fetchone()
        if(result[0] is not None):
            ret = result[0]
        return ret
    
    @classmethod
    def deleteRefItem(cls, obj): 
        id = obj.getValue(obj.COL_ID)
        CRelFundCustomer.quickDelete({CRelFundCustomer.COL_VOUCHERID:id})
        
        # 不能简单删除资金明细，只要删除关系
        #CDataAccountDetail.quickDelete({CDataAccountDetail.COL_FUNDID:id})
        CDataAccountDetail.detachVoucher(id)
        
    @classmethod
    def deepDelData(cls, voucherId):
        '''深度删除，把相关表数据一起删除'''
        cls.deepDeleteById(voucherId)
##        CRelFundCustomer.quickDelete({CRelFundCustomer.COL_VOUCHERID:voucherId})
##        CDataAccountDetail.quickDelete({CDataAccountDetail.COL_FUNDID:voucherId})
##        cls.quickDelete({cls.COL_ID:voucherId})
    
    def getAccountName(self):
        accountObj = CDataAccount.getData({"id":self.getValue(self.iAccountId)})#.findObj(self.getValue(self.iAccountId))
        return accountObj.getValue(accountObj.COL_BRIEFNAME)
    
    @classmethod
    def getPeerName(cls, voucherId):
        voucherObj = cls.findObj(voucherId)
        if voucherObj is None:
            return "None"
        peerObj = CDataCustomer.getData({"id":voucherObj.getValue(voucherObj.iPeerId)})#findObj(voucherObj.getValue(voucherObj.iPeerId))
        return peerObj.getValue(peerObj.COL_NAME)
    
    @staticmethod
    def valid_alloct():
        sql = '''select customer.name,
                        voucher.id, 
                        voucher.money, 
                        sum(fund_customer.money) as money2, 
                        (voucher.money-sum(fund_customer.money)) as diff
                from voucher,fund_customer,customer
                where voucher.id = fund_customer.voucherId    
                    and  voucher.peerId=  customer.id        
                group by voucher.id'''
                
        cnt = dbmngr.dbmngr.execute(sql)
        if cnt == 0:
            return []
        
        ret =  dbmngr.dbmngr.cursor.fetchall()
        return ret
    
    @staticmethod
    def get_alloct(voucher_id):
        sql = '''SELECT s.id,s.total,s.discount,total_paid-money,money,s.total-s.discount-total_paid
                FROM fund_customer f 
                LEFT JOIN saleorder s ON f.billId=s.id
                LEFT JOIN (SELECT billId,SUM(money) as total_paid 
                            FROM fund_customer 
                            WHERE billId IN (SELECT billId FROM fund_customer WHERE voucherId='%s')
                            GROUP BY billId) t ON t.billId=f.billId
                WHERE voucherId='%s'
                ORDER BY s.id DESC''' %(voucher_id, voucher_id)    
         
        cnt = dbmngr.dbmngr.execute(sql)
        print sql
        print 'cnt %d' %cnt
        if (cnt != 0): 
            return dbmngr.dbmngr.cursor.fetchall()
        else:
            return []

            
class CDataVoucherSupplier(CDataModel):
    tbl = 'voucher_supplier'
    m_dataList = []
    m_fieldNo = {}
    m_fieldNames = []
    m_fieldDefVals = []
    autoId = False
    prefix = 'GF'
    suffix = '0000'
    listenerList = []
    b_basic_data = False

    # 为了和CDataVoucher保持一致
    [COL_ID,COL_PEERID,COL_ACCOUNTID,COL_MONEY,COL_DATE,COL_HANDLERID,
    COL_RECORDERID,COL_MEMO,COL_CHECK_DATE] = [i for i in range(9)]
    
    # def __init__(self):
        # CDataModel.__init__(self)
        # self.m_fieldVals = [''] * len(self.m_fieldNames)
    
    @classmethod
    def makeField(cls):
        '''系统启动时调用子类的该函数完成初始化'''
        #cls.m_fieldNames = []
        cls.iId = cls.appendField('id')
        cls.iPeerId = cls.appendField('peerId')
        cls.iAccountId = cls.appendField('accountId')
        cls.iMoney = cls.appendField('money')
        cls.iDate = cls.appendField('date')
        cls.iHandlerId = cls.appendField('handlerId')
        cls.iRecorderId = cls.appendField('recorderId')
        cls.iMemo = cls.appendField('memo')
        cls.iCheckDate = cls.appendField('check_date')
        
        #cls.refreshDataList()
    
    def getAccountDetail(self):
        return CDataAccountDetail.getObjs({CDataAccountDetail.COL_FUNDID:self.getKeyVal()})
        
    def getAccountName(self):
        accountObj = CDataAccount.getData({"id":self.getValue(self.iAccountId)})#findObj(self.getValue(self.iAccountId))
        return accountObj.getValue(accountObj.COL_BRIEFNAME)
    
    @classmethod
    def getPeerName(cls, voucherId):
        voucherObj = cls.findObj(voucherId)
        peerObj = CDataSupplier.getData({"id":voucherObj.getValue(voucherObj.iPeerId)})#findObj(voucherObj.getValue(voucherObj.iPeerId))
        return peerObj.getValue(peerObj.COL_NAME)
    
    @classmethod
    def getPaid(cls, peerId):
        ret = 0
        sql = 'select sum(money) from %s where peerId=%s' %(cls.tbl,peerId)
        cnt = dbmngr.dbmngr.execute(sql)
        #Maybe for sum() was used, there will be return for sure,
        #(None)will be returned if the custid unfound
        result =  dbmngr.dbmngr.cursor.fetchone()
        if(result[0] is not None):
            ret = result[0]
        return ret
    
    @classmethod
    def deleteRefItem(cls, obj):
        id = obj.getValue(obj.COL_ID)
        CRelFundSupplier.delData({CRelFundSupplier.COL_VOUCHERID:id})
        CDataAccountDetail.delData({CDataAccountDetail.COL_FUNDID:id})
    
    @classmethod
    def deepDelData(cls, voucherId):
        '''深度删除，把相关表数据一起删除'''
        cls.deepDeleteById(voucherId)
        
    def getBillId(self):
        '''获取关联的订单id列表'''
        return CRelFundSupplier.getBillId(self.getValue(self.COL_ID))
    
    def getAllocateInfo(self):
        return CRelFundSupplier.getAllocateInfo(self.getValue(self.COL_ID))

    @staticmethod
    def valid_alloct():
        sql = '''select supplier.name,
                        voucher_supplier.id, 
                        voucher_supplier.money, 
                        sum(fund_supplier.money) as money2, 
                        (voucher_supplier.money-sum(fund_supplier.money)) as diff
                from voucher_supplier,fund_supplier, supplier
                where voucher_supplier.id = fund_supplier.voucherId    
                    and  voucher_supplier.peerId=  supplier.id        
                group by voucher_supplier.id'''
                
        cnt = dbmngr.dbmngr.execute(sql)
        if cnt == 0:
            return []
        
        ret =  dbmngr.dbmngr.cursor.fetchall()
        return ret
    
    @staticmethod
    def get_alloct(voucher_id):
        sql = '''SELECT s.id,s.total,s.discount,total_paid-money,money,s.total-s.discount-total_paid
                FROM fund_supplier f 
                LEFT JOIN purorder s ON f.billId=s.id
                LEFT JOIN (SELECT billId,SUM(money) as total_paid 
                            FROM fund_supplier 
                            WHERE billId IN (SELECT billId FROM fund_supplier WHERE voucherId='%s')
                            GROUP BY billId) t ON t.billId=f.billId
                WHERE voucherId='%s'
                ORDER BY s.id DESC''' %(voucher_id, voucher_id)    
         
        cnt = dbmngr.dbmngr.execute(sql)
        print sql
        print 'cnt %d' %cnt
        if (cnt != 0): 
            return dbmngr.dbmngr.cursor.fetchall()
        else:
            return []
            
            
class CDataAccountSubject(CDataModel):
    tbl = 'account_subject'
    m_dataList = []
    m_fieldDefVals = []
    
    @classmethod
    def makeField(cls):
        '''系统启动时调用子类的该函数完成初始化'''
        #cls.m_fieldNames = []
        #cls.m_fieldCNNames = []
        cls.fi_id = cls.appendField('id', u'编号')
        cls.fi_name = cls.appendField('name', u'科目名称')
        cls.fi_income = cls.appendField('income', u'收支类型')
        
        cls.refreshDataList()
    
    def getValue(self, index):
        if index == self.fi_income:
            return u'收入' if self.m_fieldVals[index] else u'支出'
        
        return self.m_fieldVals[index]
    
    @classmethod
    def getDefName(cls, income=True):
        return u'主营业务收入' if income else u'主营业务支出'
            
    @classmethod
    def fetchSubjectList(cls, income):
        sql = 'select id,name from %s where income=%d' %(cls.tbl, income)
        cnt = dbmngr.dbmngr.execute(sql)        
        return dbmngr.dbmngr.cursor.fetchall()
                        
class CDataAccount(CDataModel):
    tbl = 'account'
    m_dataList = []
    m_fieldNo = {}
    m_fieldNames = ['id', 'briefName', 'fullName','bIncome', 'bDefray','memo', 'openingBalance', 'disabled']
    m_fieldCNNames = [u'编号', u'账户简名',  u'账户全名', u'', u'', u'备注', u'', u'禁用']
    m_fieldDefVals = [-1, '', '', 'T', 'T', '', 0, 0]
    [COL_ID,COL_BRIEFNAME,COL_FULLNAME,COL_BINCOME,COL_BDEFRAY,
    COL_MEMO, COL_INITAMOUNT, COL_DISABLED] = [i for i in range(8)]

    YiTianDa = 5
    TongLai = 15

    
    def __init__(self):    
        CDataModel.__init__(self)
        #初始化实例对象时，应该初始化其数据，主要目的是初始化非字符串对象的数据类型
        #除非在数据模型中存在的全部是string类型
        # 140711 每次新建对象申请id,会导致导入数据库存在大量没有必要的操作
        #        所以为之新增函数
        #id = self.newAutoIncrId()
        self.m_fieldVals = [-1, '', '', 'T', 'T', '', 0, 0]
    
    def assignNewId(self):
        id = self.newAutoIncrId()
        self.setValue(self.COL_ID, id)
        return id
        
    @staticmethod
    def getDefAccount():
        return u'现金'

    @classmethod
    def getDetailFromBill(cls, dateStart, dateEnd, accountId=None):
        print (dateStart, dateEnd, accountId)
        sql_voucher = "date between '%s' and '%s'" %(dateStart, dateEnd)
            
        if (accountId is not None and accountId > 0):
            sql_voucher += ' and account.id=%d' %(accountId)

        sql = '''SELECT account.briefName,date,money,0,customer.name,staff.name,voucher.memo
                FROM voucher
                    LEFT JOIN account ON accountId=account.id
                    LEFT JOIN customer ON peerId=customer.id
                    LEFT JOIN staff ON handlerId=staff.id
                WHERE %s
                UNION
                SELECT account.briefName,date,0,money,supplier.name,staff.name,voucher_supplier.memo
                FROM voucher_supplier
                    LEFT JOIN account ON accountId=account.id
                    LEFT JOIN supplier ON peerId=supplier.id
                    LEFT JOIN staff ON handlerId=staff.id
                WHERE %s
                UNION
                SELECT account.briefName,date,0,amount,expense.memo,staff.name,account_subject.name
                FROM expense
                LEFT JOIN account ON account_id=account.id
                    LEFT JOIN staff ON creater=staff.id
                    LEFT JOIN account_subject ON `subject`=account_subject.Id
                WHERE %s'''%(sql_voucher,sql_voucher,sql_voucher)
        print sql
        cnt = dbmngr.dbmngr.execute(sql)
        return dbmngr.dbmngr.cursor.fetchall()
    
    

class CDataAccountDetail(CDataModel):
    tbl = 'accountDetails'
    m_dataList = []
    m_fieldNo = {}
    m_fieldNames = ['id','accountId', 'date', 'money', 'fundId','memo','sysmemo',
                    'time', 'subject','loadIn','useage']
    #m_fieldCNNames = [u'编号', u'账户简名',  u'账户全名', u'', u'', u'备注']
    m_fieldDefVals = [-1, '', '', '', None, '','',None,None,0,'']
    [COL_ID,COL_ACCOUNTID,COL_DATE,COL_MONEY,COL_FUNDID,
    COL_MEMO,COL_SYSMEMO, COL_TIME, COL_SUBJECT,COL_LOADIN,COL_USEAGE] = [i for i in range(11)]
    b_basic_data = False
    
##    def __init__(self):    
##        CDataModel.__init__(self)
##        #初始化实例对象时，应该初始化其数据，主要目的是初始化非字符串对象的数据类型
##        #除非在数据模型中存在的全部是string类型
##        # 140711 每次新建对象申请id,会导致导入数据库存在大量没有必要的操作
##        #        所以为之新增函数
##        #id = self.newAutoIncrId()
##        self.m_fieldVals = [-1, '', '', '', '', '','','','']
     
##    def assignNewId(self):
##        id = self.newAutoIncrId()
##        self.setValue(self.COL_ID, id)
##        return id
    
##    @classmethod
##    def createNew(cls):
##        obj = cls()
##        obj.setValue(cls.COL_ID, cls.newAutoIncrId())
##        return obj
    
    @classmethod
    def getMaxDate(cls, accountId):
        sql = 'select max(date) from %s where accountId=%d' %(cls.tbl, accountId)
        cnt = dbmngr.dbmngr.execute(sql)
        return dbmngr.dbmngr.cursor.fetchone()[0]
    
    def getAccount(self):
        return CDataAccount.getFirstInstance({CDataAccount.COL_ID:self.m_fieldVals[self.COL_ACCOUNTID]})
    
    def getSubjectName(self):
        subjectId = self.getValue(self.COL_SUBJECT)
        if subjectId is None or subjectId == '':
            return ''
            
        subjectObj = CDataAccountSubject.findObj(subjectId)
        return subjectObj.getValue(subjectObj.fi_name)
    
    @classmethod
    def detachVoucher(cls, voucherId):
        objList = cls.getObjs({cls.COL_FUNDID:voucherId})
        if (len(objList) > 1):
            msg = u'Error:voucherId:%s match more than one accountdetail%s' %(voucherId, objList)
            kit.showModal(msg)
            
        for obj in objList:
            obj.setValue(obj.COL_FUNDID, None)
            obj.update()
    
    
    
class CDataCompany(CDataModel):
    tbl = 'company'
    m_dataList = []
    m_fieldNo = {}
    m_fieldNames = ['id', 'name', 'briefName', 'phone', 'addr', 'business']
    m_fieldDefVals = ['']*6
    [COL_ID, COL_NAME, COL_BRIEFNAME,COL_PHONE,COL_ADDR,COL_BUSINESS] = [i for i in range(6)]
    
    @classmethod
    def getCompanyInfo(cls):
        return cls.m_dataList[0]
    
class CDataStaff(CDataModel):
    tbl = 'staff'
    m_dataList = []
    m_fieldNo = {}
    m_fieldNames = ['id','name','post','phone','addr','memo',
                    'emergency', 'isOperator','user','psw', 'limited', 'disable', 'job']
    m_fieldCNNames = [u'id', u'员工姓名',  u'职务', u'联系电话', u'联系地址', 
                    u'备注', u'紧急联系', u'是否操作员', u'', u'', u'停用', u'工作类型']
    m_fieldDefVals = [-1, '', '', '', '', '', '', 'F', '', '', 1, 0, 1]
    [COL_ID,COL_NAME,COL_POST,COL_PHONE,COL_ADDR,COL_MEMO,COL_EMERGENCY,
    COL_ISOPERATOR, COL_USER, COL_PSW, COL_LIMITED, COL_DISABLE, COL_JOB] = [i for i in range(13)]

    def __init__(self):    
        CDataModel.__init__(self)
        #初始化实例对象时，应该初始化其数据，主要目的是初始化非字符串对象的数据类型
        #除非在数据模型中存在的全部是string类型
        # 140711 每次新建对象申请id,会导致导入数据库存在大量没有必要的操作
        #        所以为之新增函数
        #id = self.newAutoIncrId()
        self.m_fieldVals = [-1, '', '', '', '', '', '', 'F', '', '', 1, 0, 1]
    
    def assignNewId(self):
        id = self.newAutoIncrId()
        self.setValue(self.COL_ID, id)
        return id
        
    @staticmethod
    def isOperator(staffObj):
        return staffObj.getFieldVal(index=CDataStaff.COL_ISOPERATOR)==1
    
    def isLimited(self):
        return True if self.getValue(self.COL_LIMITED) else False
    
    @classmethod
    def getName(cls, id):

        if id is None:
            return ""
        obj = cls.getData({"id":id})#findItemValue({cls.COL_ID:id}, cls.COL_NAME)
        return obj.getValue(obj.COL_NAME) if obj is not None else ""
        
    def getValue(self, index):
        if (index == self.COL_ISOPERATOR):
            if (self.m_fieldVals[index] == 'T'):
                return u'是'
            else:
                return u'否'
            
        return self.m_fieldVals[index]
    
    @classmethod
    def login(cls, user, psw):
        for obj in cls.m_dataList:
            if (obj.m_fieldVals[cls.COL_ISOPERATOR] == 'T'):
                if (obj.m_fieldVals[cls.COL_USER] == user and
                    obj.m_fieldVals[cls.COL_PSW] == psw):
                    return obj
        return None
        
    def isLimited(self):
        return self.m_fieldVals[self.COL_LIMITED]



     
class CDataEvent(CDataModel):
    tbl = 'event'
    m_dataList = []
    m_fieldNo = {}
    m_fieldNames = ['id','title','content','bootAlarm', 'startDate']
    m_fieldDefVals = ['']*5
    [iId, iTitle, iContent, ibootAlarm, iStartDate] = [i for i in range(5)]
    autoId = True
    
    m_fieldCNNames = [u'编号', u'标题',  u'内容', u'是否提醒', u'开始时间']
                    
    def getValue(self, index):
        if (index == self.ibootAlarm):
            if (self.m_fieldVals[index] == 'T'):
                return u'是'
            else:
                return u'否'
            
        return super(CDataEvent, self).getValue(index)  
    
    @classmethod
    def getExpiredEvent(cls, now, last=None):
        sql = "select id,title,content,startDate from %s where startDate<='%s'"%(cls.tbl, now)
        if (last is not None):
            sql += " and startDate>'%s'" %last 
             
        cnt = dbmngr.dbmngr.execute(sql)
        if (0 == cnt):
            return []
        
        return dbmngr.dbmngr.cursor.fetchall()
    
    @classmethod
    def getExpiredNum(cls, now, last=None):
        sql = "select count(*) from %s where startDate<='%s'"%(cls.tbl, now)
        if (last is not None):
            sql += " and startDate>'%s'" %last 
             
        dbmngr.dbmngr.execute(sql)
        return dbmngr.dbmngr.cursor.fetchall()[0]

class CDataRecord(CDataModel):
    tbl = 'record'
    m_dataList = []
    m_fieldDefVals = []
    
    @classmethod
    def makeField(cls):
        '''系统启动时调用子类的该函数完成初始化'''
        #cls.m_fieldNames = []
        #cls.m_fieldCNNames = []
        cls.fi_id = cls.appendField('id', u'编号')
        cls.fi_title = cls.appendField('title', u'标题')
        cls.fi_type = cls.appendField('type', u'类型')
        cls.fi_content = cls.appendField('content', u'内容')
        cls.fi_feedback = cls.appendField('feedback', u'反馈')
        
        cls.refreshDataList()

    @classmethod
    def getNote(cls,id):
        obj = CDataRecord.getData({"id":id})
        return obj.getValue(CDataRecord.fi_content)


class CDataLog(CDataModel):
    tbl = 'log'
    m_dataList = []
    m_fieldDefVals = []
    @classmethod
    def makeField(cls):
        '''系统启动时调用子类的该函数完成初始化'''
        #super(CDataLog, cls).initClsData()
        cls.fi_id = cls.appendField('id', u'编号')
        cls.fi_date = cls.appendField('date', u'时间')
        cls.fi_level = cls.appendField('level', u'级别')
        cls.fi_log = cls.appendField('log', u'内容')
        
        # 数量多了之后会影响启动效率
        #cls.refreshDataList()

    @classmethod
    def add(cls, lvl, fmt, args):
        if (len(args) == 1
            and (isinstance(args[0], tuple) or isinstance(args[0], list))):
            param = args[0]
        else:
            param = args
               
        #print fmt
        #print param
        log = fmt %param
        cls.quickInsert({cls.fi_date:datetime.datetime.today(),
                        cls.fi_level:lvl,
                        cls.fi_log:log})   
        
    @classmethod
    def debug(cls, fmt, *args):
        cls.add('debug', fmt, args)
        
    @classmethod
    def info(cls, fmt, *args):
        cls.add('info', fmt, args)
        
    @classmethod
    def error(cls, fmt, *args):
        cls.add('error', fmt, args)
        
    @classmethod
    def warning(cls, fmt, *args):
        cls.add('warning', fmt, args)
        
    @classmethod
    def critical(cls, fmt, *args):
        cls.add('critical', fmt, args)
        
    @classmethod
    def exception(cls, fmt, *args):
        cls.add('exception', fmt, args)
        
class CDataTip(CDataModel):
    tbl = 'tip'
    m_dataList = []
    m_fieldDefVals = []
    @classmethod
    def makeField(cls):
        '''系统启动时调用子类的该函数完成初始化'''
        #cls.m_fieldNames = []
        #cls.m_fieldCNNames = []
        cls.fi_id = cls.appendField('id', u'编号')
        cls.fi_tip = cls.appendField('tip', u'')
        
        cls.refreshDataList()
        
    @classmethod
    def getRandomTip(cls):
        import random
        index = random.randint(1, len(cls.m_dataList) - 1)
        return cls.m_dataList[index].getValue(cls.fi_tip)
                                                  
##class CDataOperator(CDataModel):
##    tbl = 'operator'
##    m_dataList = []
##    m_fieldNo = {}
##    m_fieldNames = ['id','staffId','user','psw']
##    [COL_ID,COL_STAFFID,COL_USER,COL_PSW] = [i for i in range(4)]

class CDataDeliveryRoute(CDataModel):
    tbl = 'delivery_route'
    autoId = True
    ROUTE_TONG = 40
    ROUTE_YI = 41
    ROUTE_TONG_RETURN = 42
    ROUTE_YI_RETURN = 43
    
    @classmethod
    def makeField(cls):
        cls.fi_id = cls.appendField('id')
        cls.fi_name = cls.appendField('name')
        cls.fi_memo = cls.appendField('memo')
        cls.fi_last_delivery = cls.appendField('last_delivery', defVal=None)
    
    @classmethod
    def getRegionList(cls, id):
        return CDataDeliveryRouteRegion.getRegionList(id)

    @classmethod
    def getRegionSom(cls, id):
        sql = """select r.regionId
                    ,r.seq
                    ,count(T.id)
                    ,customer_region.name
                    ,date_format(customer_region.last_delivery, '%%Y-%%m-%%d')
                    ,sum(if(DATEDIFF(CURDATE(),T.lastOrder) <= 180,1,0)) as active_customer
                    ,ROUND(SUM(eval_sale),0)
                    ,CONVERT(ROUND(SUM(actual_sale),0), DECIMAL)
                    ,ROUND(SUM(actual_sale) / SUM(eval_sale),2)
                from delivery_route_region r 
                    left join customer_region on r.regionId=customer_region.Id
                    LEFT JOIN (SELECT customer.id,customer.region, SUM(total-discount) as actual_sale, CONVERT(lvl_potential, DECIMAL) as eval_sale,lastOrder
                            FROM customer LEFT JOIN saleorder ON saleorder.peerId=customer.id AND (DATEDIFF(CURDATE(),billDate)<365 OR billDate is NULL) 
                            WHERE type=4  GROUP BY customer.id) T ON T.region=r.regionId
                where routeId=%d 
                group by r.regionId 
                order by seq""" %(id)
        cnt = dbmngr.dbmngr.execute(sql)
        return dbmngr.dbmngr.cursor.fetchall()

    
    @classmethod
    def getRegionListStr(cls, id):
        sql = "select b.name from delivery_route_region a, customer_region b "\
            "where a.routeId=%d and a.regionId=b.id "\
            "order by a.seq" %(id)
        cnt = dbmngr.dbmngr.execute(sql)
        if cnt <= 0:
            return []
    
        ret = dbmngr.dbmngr.cursor.fetchall()
        str = u''
        for item in ret:
            str += item[0] + u','
        return str[:-1]
 
    
    @classmethod
    def deleteRefItem(cls, obj):
        '''子类根据需要选择删除关联数据表中的数据'''
        # 删除delivery_route_region
        CDataDeliveryRouteRegion.quickDelete({CDataDeliveryRouteRegion.fi_routeId:obj.getKeyVal()})
            
    @staticmethod
    def queryRouteSom():
        sql = """SELECT R.Id,R.name,date_format(R.last_delivery, '%Y-%m-%d'),R.memo, count(T.id),
                 ROUND(SUM(eval_sale),0),CONVERT(ROUND(SUM(actual_sale),0), DECIMAL)
                 ,ROUND(SUM(actual_sale) / SUM(eval_sale),2)
            FROM delivery_route R 
                LEFT JOIN delivery_route_region DR ON R.Id=DR.routeId 
                LEFT JOIN customer_region CR ON DR.regionId=CR.Id
                LEFT JOIN (SELECT customer.id,customer.region, SUM(total-discount) as actual_sale, CONVERT(lvl_potential, DECIMAL) as eval_sale
                    FROM customer LEFT JOIN saleorder ON saleorder.peerId=customer.id AND (DATEDIFF(CURDATE(),billDate)<365 OR billDate is NULL)
                    WHERE type=4  GROUP BY customer.id) T ON T.region=DR.regionId
            GROUP BY R.Id"""
        cnt = dbmngr.dbmngr.execute(sql)
        return dbmngr.dbmngr.cursor.fetchall()

class CDataDeliveryRouteRegion(CDataModel):
    tbl = 'delivery_route_region'
    fi_key=-1
    
    @classmethod
    def makeField(cls):
        cls.fi_routeId = cls.appendField('routeId')
        cls.fi_regionId = cls.appendField('regionId')
        cls.fi_index = cls.appendField('seq')

    @classmethod
    def getRegionList(cls, routeId):
        sql = "select r.regionId,r.seq,count(c.id),customer_region.name,customer_region.last_delivery "\
            ",sum(if(DATEDIFF(CURDATE(),c.lastOrder) <= 180,1,0)) as active_customer "\
            "from %s r "\
            "left join customer c on r.regionId=c.region and type=4 "\
            "left join customer_region on r.regionId=customer_region.Id "\
            "where routeId=%d "\
            "group by r.regionId "\
            "order by seq " %(cls.tbl, routeId)
        cnt = dbmngr.dbmngr.execute(sql)
        return dbmngr.dbmngr.cursor.fetchall()
        #sql = "select regionId from %s where routeId='%s' order by index asc" %(cls.tbl, routeId)
##        sql = "select b.name from delivery_route_region a, customer_region b "\
##            "where a.routeId='%s' and a.regionId=b.id "\
##            "order by a.index asc" %(routeId)
##        cnt = dbmngr.dbmngr.execute(sql)
##        if cnt <= 0:
##            return []
##    
##        ret = dbmngr.dbmngr.cursor.fetchall()
##        str = u''
##        for item in ret:
##            str += item[0] + u','
##        return str[:-1]


class CDataDeliveryPlan(CDataModel):
    tbl = 'delivery_plan'
    
    autoId = False
    prefix = 'ZS'
    suffix = '000'
    maxBillId = ''
    #记录已经申请的ID数量，避免同时实例化多个变量出现的ID重复申请
    #Note: 子类中应该重新声明该变量，以免被其他子类修改
    billIdCnt = 0
    b_basic_data = False
    
    @classmethod
    def makeField(cls):
        cls.fi_id = cls.appendField('id')
        cls.fi_planDate = cls.appendField('plan_date')
        cls.fi_sendDate = cls.appendField('send_date')
        cls.fi_returnDate = cls.appendField('return_date')
        cls.fi_truck = cls.appendField('truck', defVal=None)
        cls.fi_driver = cls.appendField('driver', defVal=None)
        cls.fi_follower1 = cls.appendField('follower1', defVal=None)
        cls.fi_follower2 = cls.appendField('follower2', defVal=None)
        cls.fi_routeId = cls.appendField('routeId')
        cls.fi_state = cls.appendField('state', defVal=None)
        cls.fi_memo = cls.appendField('memo')
        cls.fi_submit_date = cls.appendField('submit_date')
        cls.fi_submitter = cls.appendField('submitter', defVal=None)
        cls.fi_km_send = cls.appendField('km_send', defVal=None)
        cls.fi_km_return = cls.appendField('km_return', defVal=None)
        cls.fi_return_memo = cls.appendField('return_memo')
        cls.fi_toll_fee = cls.appendField('toll_fee', defVal=0.0)
        cls.fi_meals_fee = cls.appendField('meals_fee', defVal=0.0)
        cls.fi_other_fee = cls.appendField('other_fee', defVal=0.0)

    @classmethod
    def tryDelete(cls, id):
        import wx

        # 查找相关数据
        #orderList = CDataSaleOrder.getObjs({CDataSaleOrder.COL_DELIVERYPLAN:id})
        sql = 'deliveryPlan="%s"'%(id)
        orderList = CDataSaleOrder.fetch(sql)
        if orderList:
            msg = u'有%d个销售单关联到该直送计划单，是否解除关联？' %(len(orderList))
            dlg = wx.MessageDialog(None, msg, u'消息框',wx.YES_NO|wx.ICON_QUESTION)
            if dlg.ShowModal() != wx.ID_YES:
                return False
            
            for obj in orderList:
                #print 
                print obj.m_fieldNames
                print obj.m_fieldVals
                obj.setValue(CDataSaleOrder.COL_DELIVERYPLAN, None)
                obj.update()
                
        cls.delData({cls.fi_id:id})
        return True  
                
        
    def getTruckName(self):
        if self.getValue(self.fi_truck):
            return self.getValue(self.fi_truck)
        else:
            return u''
        
    def getRouteName(self):
        routeId = self.getValue(self.fi_routeId)
        routeObj = CDataDeliveryRoute.findObj(routeId)
        return routeObj.getValue(routeObj.fi_name)
    
    def getStateName(self):
        stateId = self.getValue(self.fi_state)
        if not stateId:
            return u''
        
        stateObj = CDataDeliveryState.findObj(stateId)
        return stateObj.getValue(stateObj.fi_name)
    
    def getDriverName(self):
        driverId = self.getValue(self.fi_driver)
        if not driverId:
            return u''
        
        obj = CDataStaff.findObj(driverId)
        return obj.getValue(obj.COL_NAME)
    
    def getFollowerName(self):
        id = self.getValue(self.fi_follower1)
        if not id:
            return u''
        
        obj = CDataStaff.findObj(id)
        return obj.getValue(obj.COL_NAME)
    
    def getSubmitterName(self):
        id = self.getValue(self.fi_submitter)
        if not id:
            return u''
        
        obj = CDataStaff.findObj(id)
        return obj.getValue(obj.COL_NAME)
        
    @classmethod
    def quickAdd(cls, routeId):
        obj = cls.factory(True)
        obj.setValue(obj.fi_planDate, datetime.datetime.today().date())
        obj.setValue(obj.fi_sendDate, datetime.datetime.today().date())
        obj.setValue(obj.fi_submit_date, datetime.datetime.today().date())
        obj.setValue(obj.fi_returnDate, datetime.datetime.today().date())
        obj.setValue(obj.fi_routeId, routeId)
        obj.setValue(obj.fi_state, CDataDeliveryState.getDefState().getKeyVal())
        obj.insert()
        return obj
        
    @classmethod
    def getNewId(cls):
        '''子类可以覆盖，以定义自己的id产生方式'''
        return CDataDeliveryPlan.newBillId(datetime.datetime.today().strftime('%Y%m%d'))
    
    def set_state(self, customer_id, field):
        sql = "insert into delivery_customer_contact "\
            "(customer_id, delivery_id,%s) values (%d,'%s',1) "\
            "on duplicate key update %s=if(%s=1,0,1) "\
            %(field, customer_id, self.getValue(self.fi_id), field, field)
            
        dbmngr.dbmngr.execute(sql)
        
    def set_memo(self, customer_id, memo):
        sql = "insert into delivery_customer_contact "\
            "(customer_id, delivery_id,memo) values (%d,'%s','%s') "\
            "on duplicate key update memo='%s'"\
            %(customer_id, self.getValue(self.fi_id), memo, memo)
            
        dbmngr.dbmngr.execute(sql)
    
    @classmethod
    def getOrderList(self, planId=None, billId=None):
        sql = "SELECT saleorder.id "\
            ",date_format(billDate, '%%Y%%m%%d') "\
            ",customer.`name` "\
            ",total "\
            ",IFNULL(SUM(fund_customer.money),0) AS paid "\
            ",IF(ABS(total - IFNULL(SUM(fund_customer.money),0) - discount) < 0.01, 0, total - IFNULL(SUM(fund_customer.money),0) - discount)"\
            ",discount "\
            ",saleorder.state "\
            ",saleorder.memo "\
            ",IF(printed=1,'O', 'x') "\
            ",wechat"\
            ",IFNULL(CONCAT(DATE_FORMAT(report_time,'%%H:%%i'),reporter_name,':',content), '') "\
            ",staff.`name` "\
            ",saleorder.peerId "\
            ",saleorder.deliveryPlan "\
            ",GROUP_CONCAT(account.briefName) "\
            ",if(abs(sales-paid+customer.debt)<0.01,0,sales-paid+customer.debt)"\
            ",dlvyInfo"\
            " FROM saleorder "\
            " LEFT JOIN customer ON peerId=customer.id "\
            " LEFT JOIN customer_log ON peerId=customer_log.customer_id and saleorder.deliveryPlan=customer_log.delivery_id "\
            " LEFT JOIN fund_customer ON saleorder.id=fund_customer.billId "\
            " LEFT JOIN staff ON handlerId=staff.id "\
            " LEFT JOIN voucher ON fund_customer.voucherId=voucher.id "\
	        " LEFT JOIN account ON account.id=voucher.accountId "\
            " left join (select peerId, sum(total)-sum(discount) as sales from saleorder where state is not NULL and state!='' group by peerId) t1 on t1.peerId=saleorder.peerId "\
            " left join (select peerId, sum(money) as paid from voucher group by peerId) t2 on t2.peerId=saleorder.peerId "\
            " WHERE %s "\
            " GROUP BY saleorder.id "\
            " ORDER BY customer.`name` "%("saleorder.deliveryPlan='%s'"%(planId) if planId is not None else "saleorder.id='%s'"%(billId))
        
        print sql
        cnt = dbmngr.dbmngr.execute(sql)
        return dbmngr.dbmngr.cursor.fetchall()
        
class CDataDeliveryState(CDataModel):
    tbl = 'delivery_state'
    
    STATE_PLAN = 1 # 计划中
    STATE_RUN = 2 # 出车中
    STATE_DONE = 3 # 已完成
    STATE_CHECK = 4 # 整理中
    STATE_CHECK_MALL = 5 # 整理-电商
    STATE_NEED_PIC = 6 # 待截图
    
    @classmethod
    def makeField(cls):
        cls.fi_id = cls.appendField('id')
        cls.fi_name = cls.appendField('name')

    @classmethod
    def getDefState(cls):
        return cls.findObj(1)
    
class CDataTruck(CDataModel):
    tbl = 'truck'
    
    @classmethod
    def makeField(cls):
        cls.fi_id = cls.appendField('id', u'车牌号')
        cls.fi_memo = cls.appendField('memo')


class CDataAccountCheck(CDataModel):
    tbl = 'account_check'
    
    @classmethod
    def makeField(cls):
        cls.fi_account_id = cls.appendField('account_id', u'账户')
        cls.fi_settle_date = cls.appendField('settle_date', u'对账日期')
        cls.fi_last_balance = cls.appendField('last_balance', u'上期余额')
        cls.fi_sale = cls.appendField('sale', u'本期销售')
        cls.fi_pur = cls.appendField('pur', u'本期采购')
        cls.fi_expense = cls.appendField('expense', u'本期费用')
        cls.fi_should_balance = cls.appendField('should_balance', u'本期余额')
        cls.fi_actual_balance = cls.appendField('actual_balance', u'实际余额')
        cls.fi_memo = cls.appendField('memo')
        cls.fi_last_date = cls.appendField('last_date', u'上期对账时间')

    @classmethod
    def GetLastCheck(cls, account_id):
        '''获取上次对账信息'''
        sql = """SELECT settle_date,acutal_balance
                FROM account_check
                WHERE account_id=%d
                ORDER BY settle_date DESC
                LIMIT 1"""%(account_id)
        
        cnt = dbmngr.dbmngr.execute(sql)
        if (cnt == 0):
            return [None,None]
        return dbmngr.dbmngr.cursor.fetchone()

    @classmethod
    def GetCurStat(cls, account_id, date_from, date_to):
        '''获取上次对账信息'''

        sql_date = "date <= '%s'"%(date_to)
        if date_from is not None:
            sql_date += " AND date > '%s'"%(date_from)

        sql = """SELECT ROUND(SUM(sale),2),ROUND(SUM(pur),2),ROUND(SUM(expense),2)
                FROM
                (SELECT SUM(money) as sale,0 as pur, 0 as expense
                FROM voucher
                WHERE accountId=%d AND %s
                UNION 
                SELECT 0,SUM(money) as pur,0
                FROM voucher_supplier
                WHERE accountId=%d AND %s
                UNION
                SELECT 0,0,SUM(amount) as expense
                FROM expense
                WHERE account_id=%d AND %s)T"""%(account_id, sql_date,
                account_id, sql_date,
                account_id, sql_date,)
        
        cnt = dbmngr.dbmngr.execute(sql)
        if (cnt == 0):
            return [0,0,0]
        return dbmngr.dbmngr.cursor.fetchone()

    @classmethod
    def getRecords(cls, account_id, date):
        '''获取对账记录'''
        sqlWhere = " 1=1 "
        if account_id != -1:
            sqlWhere += " and account_id=%d "%(account_id)
        elif date != "" and date is not None:
            sqlWhere += " and settle_date='%s' "%(date)

        sql = """SELECT account_id
                    ,briefName
                    ,settle_date
                    ,acutal_balance
                    ,should_balance
                    ,should_balance-acutal_balance
                    ,account_check.memo
                    ,last_date
                    ,last_balance
                    ,sale
                    ,pur
                    ,expense
                FROM account_check LEFT JOIN account ON account_id=id
                WHERE %s 
                order by settle_date desc """%(sqlWhere)
        print sql
        cnt = dbmngr.dbmngr.execute(sql)
        return dbmngr.dbmngr.cursor.fetchall()

    @classmethod
    def deleteItem(cls, account_id, check_date):
        sql = """DELETE 
                FROM account_check
                WHERE account_id=%d AND settle_date='%s'"""%(account_id, check_date)
        
        cnt = dbmngr.dbmngr.execute(sql)

    @classmethod
    def GetSettleDate(cls):
        sql = """SELECT distinct settle_date
                FROM account_check
                order by settle_date desc """
        
        cnt = dbmngr.dbmngr.execute(sql)
        return dbmngr.dbmngr.cursor.fetchall()

class CDataWarehouse(CDataModel):
    tbl = 'warehouse'
    wh_main_id = 1
    WH_JINBO = 1
    WH_WUMING = 2
    WH_CHEGUANSUO = 11
    
    @classmethod
    def makeField(cls):
        cls.fi_id = cls.appendField('id', u'仓库ID')
        cls.fi_name = cls.appendField('name', u'仓库名称')
        cls.fi_code = cls.appendField('briefCode', u'仓库代码')
        cls.fi_addr = cls.appendField('addr', u'仓库地址')
        cls.fi_memo = cls.appendField('memo')

    @classmethod
    def getDefault(cls):
        return cls.findObj(cls.wh_main_id)

class CDataTableSale(CDataModel):
    tbl = 'table_sale'
    m_dataList = []
    m_fieldNo = {}
    m_fieldDefVals = []
    #m_fieldNames = ['id', 'title','footer','buyer','debt']
    #[iId, iTitle, iFooter, iBuyer] = [i for i in range(4)]
    autoId = True
    
    @classmethod
    def makeField(cls):
        '''系统启动时调用子类的该函数完成初始化'''
        #cls.m_fieldNames = []
        cls.iId = cls.appendField('id')
        cls.iTitle = cls.appendField('title')
        cls.iFooter = cls.appendField('footer')
        cls.iBuyer = cls.appendField('buyer')
        cls.iDebt = cls.appendField('debt')
        cls.iPhone = cls.appendField('phone')
        
        cls.refreshDataList()
            
    @classmethod
    def getTableSale(cls):
        return cls.m_dataList[0]
    
    @classmethod
    def updateData(cls, title, footer, buyer, debt, phone):
        cls.m_dataList[0].m_fieldVals[cls.iTitle] = title
        cls.m_dataList[0].m_fieldVals[cls.iFooter] = footer
        
        if (buyer):
            cls.m_dataList[0].m_fieldVals[cls.iBuyer] = 'T'
        else:
            cls.m_dataList[0].m_fieldVals[cls.iBuyer] = 'F'
            
        if (debt):
            cls.m_dataList[0].m_fieldVals[cls.iDebt] = 'T'
        else:
            cls.m_dataList[0].m_fieldVals[cls.iDebt] = 'F'
                
        if (phone):
            cls.m_dataList[0].m_fieldVals[cls.iPhone] = 'T'
        else:
            cls.m_dataList[0].m_fieldVals[cls.iPhone] = 'F'
            
        cls.m_dataList[0].update()
    
    @classmethod
    def includeBuyer(cls):
        if (cls.m_dataList[0].m_fieldVals[cls.iBuyer] == 'T'):
            return True
        else:
            return False
        
    @classmethod
    def includeDebt(cls):
        if (cls.m_dataList[0].m_fieldVals[cls.iDebt] == 'T'):
            return True
        else:
            return False
    
    @classmethod
    def includePhone(cls):
        if (cls.m_dataList[0].m_fieldVals[cls.iPhone] == 'T'):
            return True
        else:
            return False


class CDataAssets(CDataModel):
    tbl = 'assets'
    
    @classmethod
    def makeField(cls):
        cls.fi_name = cls.appendField('id', u'表名')
        cls.fi_totalCurrentAssets = cls.appendField('totalCurrentAssets', u'流动资产合计')
        cls.fi_cash = cls.appendField('cash', u'货币资金')
        cls.fi_cashMemo = cls.appendField('cashMemo', u'货币资金备注')
        cls.fi_accountRecv = cls.appendField('accountRecv', u'应收账款')
        cls.fi_accountRecvMemo = cls.appendField('accountRecvMemo', u'应收账款备注')
        cls.fi_yunxingDebts = cls.appendField('yunxingDebts', u'云星应收')
        cls.fi_yunxingDebtsMemo = cls.appendField('yunxingDebtsMemo', u'云星应收备注')
        cls.fi_badDebts = cls.appendField('badDebts', u'呆账')
        cls.fi_badDebtsMemo = cls.appendField('badDebtsMemo', u'呆账备注')
        cls.fi_accountPrepaid = cls.appendField('accountPrepaid', u'预付账款')
        cls.fi_accountPrepaidMemo = cls.appendField('accountPrepaidMemo', u'预付账款备注')
        cls.fi_inventories = cls.appendField('inventories', u'存货')
        cls.fi_inventoriesMemo = cls.appendField('inventoriesMemo', u'存货备注')
        cls.fi_otherCurrentAssets = cls.appendField('otherCurrentAssets', u'其他流动资产')
        cls.fi_otherCurrentAssetsMemo = cls.appendField('otherCurrentAssetsMemo', u'其他流动资产备注')
        cls.fi_totalNonCurentAssets = cls.appendField('totalNonCurentAssets', u'非流动资产合计')
        cls.fi_fixedAssets = cls.appendField('fixedAssets', u'固定资产')
        cls.fi_fixedAssetsMemo = cls.appendField('fixedAssetsMemo', u'固定资产备注')
        cls.fi_quipOriPrice = cls.appendField('quipOriPrice', u'设备原价')
        cls.fi_quipOriPriceMemo = cls.appendField('quipOriPriceMemo', u'设备原价备注')
        cls.fi_quipDepreciation = cls.appendField('quipDepreciation', u'设备折旧')
        cls.fi_quipDepreciationMemo = cls.appendField('quipDepreciationMemo', u'设备折旧备注')
        cls.fi_totalAssets = cls.appendField('totalAssets', u'资产合计')
        cls.fi_totalCurrentLiabilities = cls.appendField('totalCurrentLiabilities', u'流动负债合计')
        cls.fi_shortTerm = cls.appendField('shortTerm', u'短期负债')
        cls.fi_shortTermMemo = cls.appendField('shortTermMemo', u'短期负债备注')
        cls.fi_accountPayable = cls.appendField('accountPayable', u'应付账款')
        cls.fi_accountPayableMemo = cls.appendField('accountPayableMemo', u'应付账款备注')
        cls.fi_advanceReceipts = cls.appendField('advanceReceipts', u'预收账款')
        cls.fi_advanceReceiptsMemo = cls.appendField('advanceReceiptsMemo', u'预收账款备注')
        cls.fi_otherPayables = cls.appendField('otherPayables', u'其他应付款')
        cls.fi_otherPayablesMemo = cls.appendField('otherPayablesMemo', u'其他应付款备注')
        cls.fi_longLiabilities = cls.appendField('longLiabilities', u'长期负债')
        cls.fi_longLiabilitiesMemo = cls.appendField('longLiabilitiesMemo', u'长期负债备注')
        cls.fi_totalOwnerEquity = cls.appendField('totalOwnerEquity', u'所有者权益合计')
        cls.fi_capital = cls.appendField('capital', u'实收股本')
        cls.fi_capitalMemo = cls.appendField('capitalMemo', u'实收股本备注')
        cls.fi_capitalReserve = cls.appendField('capitalReserve', u'资本公积')
        cls.fi_capitalReserveMemo = cls.appendField('capitalReserveMemo', u'资本公积备注')
        cls.fi_earningReserve = cls.appendField('earningReserve', u'盈余公积')
        cls.fi_earningReserveMemo = cls.appendField('earningReserveMemo', u'盈余公积备注')
        cls.fi_retainedEarnings = cls.appendField('retainedEarnings', u'未分配利润')
        cls.fi_retainedEarningsMemo = cls.appendField('retainedEarningsMemo', u'未分配利润备注')
        cls.fi_totalBadDebts = cls.appendField('totalBadDebts', u'坏账合计')
        cls.fi_totalBadDebtsMemo = cls.appendField('totalBadDebtsMemo', u'坏账备注')
        cls.fi_memo = cls.appendField('memo', u'备注')

    @staticmethod
    def GetAccountReceivable():
        ''' 应收帐款 '''

        sql = """SELECT round(initdebt+should-paid, 0)
                FROM
                (SELECT SUM(debt) as initdebt
                FROM customer) T1,
                (SELECT SUM(total-discount) as should
                FROM saleorder) T2,
                (SELECT SUM(money) as paid
                FROM voucher) T3"""

        dbmngr.dbmngr.execute(sql)
        return dbmngr.dbmngr.cursor.fetchall()[0][0]

    @staticmethod
    def GetAccountPrepaid():
        ''' 预付帐款 '''

        sql = """SELECT ROUND(SUM(money),0)
                FROM purorder LEFT JOIN fund_supplier ON purorder.id=fund_supplier.billId
                WHERE wh_in IS NULL"""

        dbmngr.dbmngr.execute(sql)
        return dbmngr.dbmngr.cursor.fetchall()[0][0]

    @staticmethod
    def GetInventories():
        ''' 存货 '''

        sql = """SELECT ROUND(sum(last_cost*qty),0)
                FROM product 
                    LEFT JOIN (SELECT product_id,SUM(quantity) as qty FROM product_extra GROUP BY product_id) T ON T.product_id=id
                WHERE categoryId NOT IN (33,34,69)"""

        dbmngr.dbmngr.execute(sql)
        return dbmngr.dbmngr.cursor.fetchall()[0][0]

    @staticmethod
    def GetAccountPayable():
        ''' 应付账款 '''

        sql = """SELECT ROUND(initdebt+should-paid,0)
                FROM
                (SELECT SUM(debt) as initdebt
                FROM supplier) T1,
                (SELECT SUM(total-discount) as should
                FROM purorder) T2,
                (SELECT SUM(money) as paid
                FROM voucher_supplier) T3"""

        dbmngr.dbmngr.execute(sql)
        return dbmngr.dbmngr.cursor.fetchall()[0][0]
    
    @staticmethod
    def GetYunxingDebts():
        ''' 应付账款 '''

        sql = """SELECT ROUND(initdebt+should-paid,0)
                FROM
                (SELECT SUM(debt) as initdebt
                FROM customer WHERE type=16) T1,
                (SELECT SUM(total-discount) as should
                FROM saleorder LEFT JOIN customer ON peerId=customer.id  WHERE type=16) T2,
                (SELECT SUM(money) as paid
                FROM voucher LEFT JOIN customer ON peerId=customer.id  WHERE type=16) T3"""

        dbmngr.dbmngr.execute(sql)
        return dbmngr.dbmngr.cursor.fetchall()[0][0]

    @staticmethod
    def GetBadDebts():
        ''' 呆账 '''

        sql = u"""SELECT ROUND(initdebt+should-paid,0)
                FROM
                (SELECT SUM(debt) as initdebt
                FROM customer WHERE name like "%%呆账%%") T1,
                (SELECT SUM(total-discount) as should
                FROM saleorder LEFT JOIN customer ON peerId=customer.id  WHERE name like "%%呆账%%") T2,
                (SELECT SUM(money) as paid
                FROM voucher LEFT JOIN customer ON peerId=customer.id  WHERE name like "%%呆账%%") T3"""

        dbmngr.dbmngr.execute(sql)
        return dbmngr.dbmngr.cursor.fetchall()[0][0]


class CDataIncomeStatement(CDataModel):
    tbl = 'income_statement'
    
    @classmethod
    def makeField(cls):
        cls.fi_id = cls.appendField('id', u'表名')
        cls.fi_dateStart = cls.appendField('dateStart', u'开始日期')
        cls.fi_dateEnd = cls.appendField('dateEnd', u'结束日期')
        cls.fi_sales = cls.appendField('sales', u'主营业务收入')
        cls.fi_discount = cls.appendField('discount', u'折扣')
        cls.fi_cost = cls.appendField('cost', u'主营业务成本')
        cls.fi_saleTax = cls.appendField('saleTax', u'营业税')
        cls.fi_grossProfit = cls.appendField('grossProfit', u'主营业务利润')
        cls.fi_otherIncome = cls.appendField('otherIncome', u'其他业务利润')
        cls.fi_expense = cls.appendField('expense', u'营业费用')
        cls.fi_operatingIncome = cls.appendField('operatingIncome', u'营业利润')
        cls.fi_nonOperatingIncome = cls.appendField('nonOperatingIncome', u'营业外收入')
        cls.fi_nonOperatingExpense = cls.appendField('nonOperatingExpense', u'营业外支出')
        cls.fi_incomeBeforeTax = cls.appendField('incomeBeforeTax', u'利润总额')
        cls.fi_incomeTax = cls.appendField('incomeTax', u'所得税')
        cls.fi_netIncome = cls.appendField('netIncome', u'净利润')
        cls.fi_memo = cls.appendField('memo', u'备注')
        cls.fi_lastInventories = cls.appendField('lastInventories', u'期初库存')
        cls.fi_purchase = cls.appendField('purchase', u'本期采购')
        cls.fi_thisInventories = cls.appendField('thisInventories', u'期末库存')

    @staticmethod
    def GetSales(dateStart, dateEnd):
        ''' 主营业务收入 '''
        sql = """SELECT round(sum(total),0),round(SUM(discount),0)
                FROM saleorder
                WHERE billDate>='%s' and billDate<='%s' AND state!=""
            """ %(dateStart, dateEnd)

        dbmngr.dbmngr.execute(sql)
        return dbmngr.dbmngr.cursor.fetchall()[0]

    @staticmethod
    def GetPurchase(dateStart, dateEnd):
        ''' 主营业务支出 '''
        sql = """SELECT round(sum(total-discount),0)
                FROM purorder
                WHERE billDate>='%s' and billDate<='%s' and wh_in is not NULL
            """ %(dateStart, dateEnd)

        dbmngr.dbmngr.execute(sql)
        return dbmngr.dbmngr.cursor.fetchall()[0][0]

    @staticmethod
    def GetExpense(dateStart, dateEnd):
        ''' 营业费用 '''
        sql = """SELECT round(SUM(amount),0)
                FROM expense
                WHERE date>= '%s' AND date<='%s' and deleter is Null
            """ %(dateStart, dateEnd)

        dbmngr.dbmngr.execute(sql)
        return dbmngr.dbmngr.cursor.fetchall()[0][0]

class CDataCashFlow(CDataModel):
    tbl = 'cash_flow'
    
    @classmethod
    def makeField(cls):
        cls.fi_id = cls.appendField('id', u'表名')
        cls.fi_dateStart = cls.appendField('dateStart', u'开始日期')
        cls.fi_dateEnd = cls.appendField('dateEnd', u'结束日期')
        cls.fi_CashFlowOperating = cls.appendField('CashFlowOperating', u'经营现金流量')
        cls.fi_CashFromSale = cls.appendField('CashFromSale', u'销售劳务现金')
        cls.fi_CashFromOther = cls.appendField('CashFromOther', u'其他营业收入')
        cls.fi_CashInSubtotal = cls.appendField('CashInSubtotal', u'流入现金小计')
        cls.fi_CashPur = cls.appendField('CashPur', u'采购支付现金')
        cls.fi_CashToEmployee = cls.appendField('CashToEmployee', u'职工开支')
        cls.fi_CashOtherOut = cls.appendField('CashOtherOut', u'其他现金支出')
        cls.fi_CashOutSubtotal = cls.appendField('CashOutSubtotal', u'流出现金小计')
        cls.fi_NetFlowOperating = cls.appendField('NetFlowOperating', u'经营净现金流')
        cls.fi_CashFlowInvest = cls.appendField('CashFlowInvest', u'投资现金流量')
        cls.fi_ReturnFromAssets = cls.appendField('ReturnFromAssets', u'处置资产收回')
        cls.fi_ReturnFromInvest = cls.appendField('ReturnFromInvest', u'其他投资收回')
        cls.fi_InvestInSubtotal = cls.appendField('InvestInSubtotal', u'投资流入小计')
        cls.fi_PaidForAsset = cls.appendField('PaidForAsset', u'购置资产支付')
        cls.fi_PaidForInvest = cls.appendField('PaidForInvest', u'其他投资支付')
        cls.fi_InvestOutSubtotal = cls.appendField('InvestOutSubtotal', u'投资流出小计')
        cls.fi_NetFlowInvest = cls.appendField('NetFlowInvest', u'投资现金流净额')
        cls.fi_CashFlowFinance = cls.appendField('CashFlowFinance', u'融资现金流量')
        cls.fi_memo = cls.appendField('memo', u'备注')

    @staticmethod
    def GetCashFromSale(dateStart, dateEnd):
        ''' 销售商品、提供劳务收到的现金 '''
        sql = """SELECT round(SUM(money),0)
                FROM voucher
                WHERE date>= '%s' AND date<='%s'
            """ %(dateStart, dateEnd)

        dbmngr.dbmngr.execute(sql)
        return dbmngr.dbmngr.cursor.fetchall()[0][0]

    @staticmethod
    def GetCashToPur(dateStart, dateEnd):
        ''' 购买商品、接受劳务支付的现金 '''
        sql = """SELECT round(SUM(money),0)
                FROM voucher_supplier
                WHERE date>= '%s' AND date<='%s'
            """ %(dateStart, dateEnd)

        dbmngr.dbmngr.execute(sql)
        return dbmngr.dbmngr.cursor.fetchall()[0][0]

    @staticmethod
    def GetCashForEmp(dateStart, dateEnd):
        ''' 支付给职工以及为职工支付的现金 '''
        sql = """SELECT round(SUM(amount),0)
                FROM expense
                WHERE subject IN (13,14) AND date>= '%s' AND date<='%s' and deleter is Null
            """ %(dateStart, dateEnd)

        dbmngr.dbmngr.execute(sql)
        return dbmngr.dbmngr.cursor.fetchall()[0][0]

    @staticmethod
    def GetCashOther(dateStart, dateEnd):
        ''' 支付其他与经营活动有关的现金 '''
        sql = """SELECT round(SUM(amount),0)
                FROM expense
                WHERE subject NOT IN (13,14) AND  date>= '%s' AND date<='%s' and deleter is Null
            """ %(dateStart, dateEnd)

        dbmngr.dbmngr.execute(sql)
        return dbmngr.dbmngr.cursor.fetchall()[0][0]


class CDataProfitProduct(CDataModel):
    tbl = 'product_profit'

    @classmethod
    def makeField(cls):
        cls.fi_id = cls.appendField('id', u'表名')
        cls.fi_dateStart = cls.appendField('dateStart', u'开始日期')
        cls.fi_dateEnd = cls.appendField('dateEnd', u'结束日期')
        cls.fi_GrossProfit = cls.appendField('GrossProfit', u'毛利润')
        cls.fi_Sales = cls.appendField('Sales', u'商品销售')
        cls.fi_Cost = cls.appendField('Cost', u'商品采购')
        cls.fi_Discount = cls.appendField('Discount', u'订单折扣')
        cls.fi_Expense = cls.appendField('Expense', u'运营费用')
        cls.fi_Employee = cls.appendField('Employee', u'员工开支')
        cls.fi_Oil = cls.appendField('Oil', u'油费差旅')
        cls.fi_Truck = cls.appendField('Truck', u'车辆维保')
        cls.fi_Invest = cls.appendField('Invest', u'投资花费')
        cls.fi_OtherExpense = cls.appendField('OtherExpense', u'其他费用')
        cls.fi_Rent = cls.appendField('Rent', u'当期租金')
        cls.fi_NetProfit = cls.appendField('NetProfit', u'净利润评估')
        cls.fi_Memo = cls.appendField('Memo', u'备注')

    @staticmethod
    def GetSales(dateStart, dateEnd):
        ''' 商品销售金额，商品最近到库价 '''
        sql = """SELECT ROUND(SUM(saledetails.total),0), ROUND(SUM(last_cost*num),0)
                FROM saledetails
                    LEFT JOIN saleorder ON saledetails.billId=saleorder.id
                    LEFT JOIN product ON proId=product.id
                    LEFT JOIN customer ON saledetails.peerId=customer.id
                WHERE billDate>= '%s' AND billDate<='%s' AND saledetails.total>0 AND last_cost>0 
                    AND type!=15 AND categoryId not in (33,34,69) 
            """ %(dateStart, dateEnd)

        dbmngr.dbmngr.execute(sql)
        return dbmngr.dbmngr.cursor.fetchall()[0]

    @staticmethod
    def GetDiscount(dateStart, dateEnd):
        ''' 订单折扣 '''
        sql = """SELECT round(SUM(discount),0)
                FROM saleorder
                WHERE billDate>='%s' and billDate<='%s' AND state!=""
            """ %(dateStart, dateEnd)

        dbmngr.dbmngr.execute(sql)
        return dbmngr.dbmngr.cursor.fetchall()[0][0]

    @staticmethod
    def GetExpense(dateStart, dateEnd):
        ''' 运营开支 '''
        sql = """SELECT 
                    SUM(CASE WHEN subject IN (13,14) THEN amount ELSE 0 END) AS employee,
                    SUM(CASE WHEN subject IN (11,12,15) THEN amount ELSE 0 END) AS truck,
                    SUM(CASE WHEN subject IN (10,23) THEN amount ELSE 0 END) AS oil,
                    SUM(CASE WHEN subject IN (26) THEN amount ELSE 0 END) AS invest,
                    SUM(CASE WHEN subject NOT IN (10,11,12,13,14,15,23,26,18) THEN amount ELSE 0 END) AS other
                FROM expense
                WHERE date>= '%s' AND date<='%s' AND deleter IS NULL
            """ %(dateStart, dateEnd)

        dbmngr.dbmngr.execute(sql)
        return dbmngr.dbmngr.cursor.fetchall()[0]


class CDataMallProfit(CDataModel):

    tbl = 'mall_profit'
    Yi_Kickback = 0.026
    Tong_Kickback = 0.05
    ROUTE_TONG = 40
    ROUTE_YI = 41

    @classmethod
    def makeField(cls):
        cls.fi_id = cls.appendField('id', u'表名')
        cls.fi_dateStart = cls.appendField('dateStart', u'开始日期')
        cls.fi_dateEnd = cls.appendField('dateEnd', u'结束日期')
        cls.fi_SalesTotal = cls.appendField('SalesTotal', u'总销售')
        cls.fi_GrossProfitYi = cls.appendField('GrossProfitYi', u'易天达毛利润')
        cls.fi_SalesYi = cls.appendField('SalesYi', u'易天达商品销售')
        cls.fi_PurchaseYi = cls.appendField('PurchaseYi', u'易天达商品采购')
        cls.fi_DiscountYi = cls.appendField('DiscountYi', u'易天达订单折扣')
        cls.fi_KickbackYi = cls.appendField('KickbackYi', u'易天达抽佣')
        cls.fi_FillbackYi = cls.appendField('FillbackYi', u'易天达回填')
        cls.fi_GrossProfitTong = cls.appendField('GrossProfitTong', u'同莱创毛利润')
        cls.fi_SalesTong = cls.appendField('SalesTong', u'同莱创商品销售')
        cls.fi_PurchaseTong = cls.appendField('PurchaseTong', u'同莱创商品采购')
        cls.fi_DiscountTong = cls.appendField('DiscountTong', u'同莱创订单折扣')
        cls.fi_KickbackTong = cls.appendField('KickbackTong', u'同莱创抽佣')
        cls.fi_FillbackTong = cls.appendField('FillbackTong', u'同莱创回填')
        cls.fi_Expense = cls.appendField('Expense', u'运营费用')
        cls.fi_MallExpense = cls.appendField('MallExpense', u'商城费用')
        cls.fi_NetProfit = cls.appendField('NetProfit', u'净利润评估')
        cls.fi_Memo = cls.appendField('Memo', u'备注')
        cls.fi_SalesTotalYi = cls.appendField('SalesTotalYi', u'易天达总额+补差')
        cls.fi_TotalYi = cls.appendField('TotalYi', u'易天达总额')
        cls.fi_TotalTong = cls.appendField('TotalTong', u'同莱创总额')

    @staticmethod
    def GetSales(dateStart, dateEnd, routeId):
        ''' 商品销售金额，商品最近到库价，不计算文字说明、赠品、定货 '''
        sql = """SELECT ROUND(SUM(saledetails.total),0), ROUND(SUM(last_cost*num),0)
                FROM saledetails
                    LEFT JOIN saleorder o ON saledetails.billId=o.id
                    LEFT JOIN delivery_plan p ON o.deliveryPlan=p.Id
                    LEFT JOIN product ON proId=product.id 
                WHERE billDate>= '%s' AND billDate<='%s' and p.routeId=%d AND categoryId not in (33,34,69)
            """ %(dateStart, dateEnd, routeId)

        cnt = dbmngr.dbmngr.execute(sql)
        print sql
        #print cnt
        if cnt == 0:
            return [0.0, 0.0]
        return dbmngr.dbmngr.cursor.fetchall()[0]

    @staticmethod
    def GetDiscount(dateStart, dateEnd, routeId):
        ''' 订单折扣 '''
        sql = """SELECT round(SUM(total),0), round(SUM(discount),0)
                FROM saleorder o
                    LEFT JOIN delivery_plan p ON o.deliveryPlan=p.Id 
                WHERE billDate>='%s' and billDate<='%s' and p.routeId=%d
            """ %(dateStart, dateEnd, routeId)

        cnt = dbmngr.dbmngr.execute(sql)
        if cnt == 0:
            return 0.0
        return dbmngr.dbmngr.cursor.fetchall()[0]


    @staticmethod
    def GetMakeDiff(dateStart, dateEnd):
        ''' 商城补差 '''
        sql = """SELECT ROUND(SUM(saledetails.total),0)
                FROM saledetails
                    LEFT JOIN saleorder o ON saledetails.billId=o.id
                WHERE proId=1797 AND billDate>= '%s' AND billDate<='%s'
            """ %(dateStart, dateEnd)

        cnt = dbmngr.dbmngr.execute(sql)
        if cnt == 0:
            return 0.0
        return dbmngr.dbmngr.cursor.fetchall()[0][0]


class CRelModel(CDataModel):
    '''对应数据库中描述实体关系的表，该类表，并不需要全部显示，仅只在条件查询时用，
        并不提供本地缓存
    '''
    
    tbl = ''
    m_fieldNames = []
    fi_key = -1
    b_basic_data = True
    
##    def __init__(self):
##        if self.__class__ is CDataModel:
##            abstract()
##
##        self.m_fieldVals = [''] * len(self.m_fieldNames)
    
    @classmethod
    def fetchObjs(cls, filterSql):
        '''从数据库中获取数据
            return - objList fetched from DB
        '''
        objList = []
        sql = u'select * from '+ cls.tbl
        if (filterSql != ''):
            sql += ' where ' + filterSql
        cnt = dbmngr.dbmngr.execute(sql)
        if (cnt == 0):
            return None
        result = dbmngr.dbmngr.cursor.fetchall()
        for item in result:
            obj = cls()
            for j in range(len(item)):
                obj.m_fieldVals[j] = item[j]
            objList.append(obj)
        return   objList
    
    @classmethod
    def concat(cls, filter):
        '''filter - {COL_xx:xx,}
            return - string
        '''
        str = ''
        for col,val in filter.items():
            str += '%s=%s and ' %(cls.m_fieldNames[col], str(val))
        
        return str[:-5]
    
    @classmethod
    def fetchRows(cls, filterSql='', fields=[]):
        '''从数据库中获取数据
            filterSql - string
            fields - [COL_xx,]
            return - objList fetched from DB
        '''
        if (len(fields) == 0):
            sql = 'select * from '+ cls.tbl
        else:
            sql = 'select '
            for i in range(len(fields)):
                sql += '%s,' %cls.m_fieldNames[fields[i]]
           
            sql = sql[:-1] + ' from ' + cls.tbl
            
        if (filterSql != ''):
            sql += ' where ' + filterSql
            
        cnt = dbmngr.dbmngr.execute(sql)
##        if (cnt == 0):
##            return []
 
        return dbmngr.dbmngr.cursor.fetchall()
            
class CRelCustDlvy(CRelModel):
    tbl = 'custDlvy'
    m_fieldNames = ['custId', 'dlvyId']
    m_fieldDefVals = ['']*2

    [COL_CUSTID, COL_DLVYID] = [0, 1]
    
    @classmethod
    def getDlvy(cls, custId):
        #objList = []
        
        #filterSql = 'custId=%d' %custId      
        #result = cls.fetchRows(filterSql, [CRelCustDlvy.COL_DLVYID])
        # print result
        # for row in result:
        #     dlvyId = row[0]
        #     objList.append(CDataDelivery.getFirstInstance({CDataDelivery.COL_ID:dlvyId}))
        #return objList

        # sql = """SELECT delivery.id,delivery.`name`,delivery.memo
        #         FROM custdlvy LEFT JOIN delivery ON custdlvy.dlvyId=delivery.id
        #         WHERE custId=%d
        #         ORDER BY delivery.id DESC""" %custId
        
        # 241017 - 改为从销售单中读取
        sql = """SELECT DISTINCT dlvyInfo
                FROM
                (SELECT id, dlvyInfo
                FROM saleorder
                WHERE peerId=%d
                AND dlvyInfo is not NULL
                ORDER BY id DESC) T
                LIMIT 20
            """ %(custId)

        cnt = dbmngr.dbmngr.execute(sql)
        if (cnt == 0):
            return []
        rows = dbmngr.dbmngr.cursor.fetchall()
        if (rows[0][0] is None):
            return []
        else:
            return rows

    
        
 
class CRelFundCustomer(CRelModel):
    tbl = 'fund_customer'
    m_fieldNames = ['billId', 'voucherId', 'money']
    m_fieldDefVals = ['']*3
    [COL_BILLID, COL_VOUCHERID, COL_MONEY] = [0,1,2]
    
    listenerList = []

    def getPeerId(self):
        voucherObj = CDataVoucher.findObj(self.getValue(self.COL_VOUCHERID))
        return voucherObj.getValue(voucherObj.iPeerId)

            
    @classmethod
    def getPaidAmount(cls, saleId):
        sql = "select sum(money) from %s where billId='%s'" %(cls.tbl, saleId)
        cnt = dbmngr.dbmngr.execute(sql)
        if (cnt == 0):
            return 0
        rows = dbmngr.dbmngr.cursor.fetchall()
        if (rows[0][0] is None):
            return 0
        return rows[0][0]
    
    @classmethod
    def getVoucherAlloct(cls, voucherId):
        sql = "select sum(money) from %s where voucherId='%s'" %(cls.tbl, voucherId)
        cnt = dbmngr.dbmngr.execute(sql)
        if (cnt == 0):
            return 0
        rows = dbmngr.dbmngr.cursor.fetchall()
        if (rows[0][0] is None):
            return 0
        return rows[0][0]
        
    @classmethod
    def getBillId(cls, voucherId):
        ids = []
        sql = "select billId from %s where voucherId='%s'" %(cls.tbl, voucherId)
        cnt = dbmngr.dbmngr.execute(sql)
        if (cnt >= 1):
            ret = dbmngr.dbmngr.cursor.fetchall()
            for i in range(cnt):
                ids.append(ret[i][0])
        return ids

    @classmethod
    def getAllocateInfo(cls, voucherId):

        sql = "select billId,money from %s where voucherId='%s'" %(cls.tbl, voucherId)
        cnt = dbmngr.dbmngr.execute(sql)
        if (cnt == 0):
            return []
        rows = dbmngr.dbmngr.cursor.fetchall()
        if (rows[0][0] is None):
            return []
        else:
            return rows
    
    @classmethod
    def getPaidList(cls, orderId):
        sql = "select voucherId,money from %s where billId='%s'" %(cls.tbl, orderId)
        cnt = dbmngr.dbmngr.execute(sql)
        if (cnt == 0):
            return []
        rows = dbmngr.dbmngr.cursor.fetchall()
        if (rows[0][0] is None):
            return []
        else:
            return rows
         
class CRelFundSupplier(CRelModel):
    tbl = 'fund_supplier'
    m_fieldNames = ['billId', 'voucherId', 'money']
    m_fieldDefVals = ['']*3
    [COL_BILLID, COL_VOUCHERID, COL_MONEY] = [0,1,2]

    listenerList = []
    
    def getPeerId(self):
        voucherObj = CDataVoucherSupplier.findObj(self.getValue(self.COL_VOUCHERID))
        return voucherObj.getValue(voucherObj.iPeerId) if voucherObj is not None else None
    
    @classmethod
    def getPaidAmount(cls, billId):
        sql = "select sum(money) from %s where billId='%s'" %(cls.tbl, billId)
        cnt = dbmngr.dbmngr.execute(sql)
        if (cnt == 0):
            return 0
        rows = dbmngr.dbmngr.cursor.fetchall()
        if (rows[0][0] is None):
            return 0
        return rows[0][0]
    
    @classmethod
    def getBillId(cls, voucherId):
        ids = []
        sql = "select billId from %s where voucherId='%s'" %(cls.tbl, voucherId)
        cnt = dbmngr.dbmngr.execute(sql)
        if (cnt >= 1):
            ret = dbmngr.dbmngr.cursor.fetchall()
            for i in range(cnt):
                ids.append(ret[i][0])
        return ids
    
    @classmethod
    def getAllocateInfo(cls, voucherId):
        sql = "select billId,money from %s where voucherId='%s'" %(cls.tbl, voucherId)
        cnt = dbmngr.dbmngr.execute(sql)
        if (cnt == 0):
            return []
        rows = dbmngr.dbmngr.cursor.fetchall()
        if (rows[0][0] is None):
            return []
        else:
            return rows
    
    @classmethod
    def getPaidList(cls, orderId):
        sql = "select voucherId,money from %s where billId='%s'" %(cls.tbl, orderId)
        cnt = dbmngr.dbmngr.execute(sql)
        if (cnt == 0):
            return []
        rows = dbmngr.dbmngr.cursor.fetchall()
        if (rows[0][0] is None):
            return []
        else:
            return rows
 
class CDataExpense(CDataModel):
    fields = [('id', None), ('date', None), ('amount', 0.00), ('account_id', None),
            ('subject', None), ('memo', ''), 
            ('creater', None), ('create_time', None),
            ('updater', None), ('update_time', None),
            ('deleter', None), ('delete_time', None), ('delete_reason',''),]
            
    tbl = 'expense'

    m_fieldNo = {}
    m_fieldNames = []
    m_fieldCNNames = []
    
    
    autoId = True
    fi_key = 0
    COL_KEY = 0
    
    @classmethod
    def initClsData(cls):   
        cls.m_fieldNames = [field for field,val in cls.fields]

    def __init__(self):
        #self.isNew = True
        for field,val in self.fields:
            self.__dict__[field] = val
            
        #self.m_fieldVals = [val for field,val in self.fields]
    
    @classmethod
    def GetSendFee(cls, month):
        '''差旅费，month 格式如 202407'''

        sql = '''SELECT SUM(toll_fee+meals_fee+other_fee)
                FROM delivery_plan
                WHERE date_format(send_date, "%%Y%%m") = "%s"''' %(month)
        cnt = dbmngr.dbmngr.execute(sql)
        if cnt==0:
            return 0
        
        res = dbmngr.dbmngr.cursor.fetchall()
        # print res
        # print res[0]
        # print res[0][0]
        return res[0][0]

    @classmethod
    def GetMallExpense(cls, dateStart, dateEnd):
        ''' 商城开支 '''

        sql = '''SELECT SUM(amount)
                FROM expense
                WHERE subject=27 and date between "%s" and "%s"''' %(dateStart, dateEnd)
        cnt = dbmngr.dbmngr.execute(sql)
        if cnt==0:
            return 0
        
        res = dbmngr.dbmngr.cursor.fetchall()
        # print res
        # print res[0]
        # print res[0][0]
        return res[0][0]

    @classmethod
    def GetMallFill(cls, month):
        ''' 商城补差折扣 '''

        sql = '''SELECT SUM(saledetails.total)
                FROM saledetails left join saleorder on saledetails.billId=saleorder.id
                WHERE proId=1797 and date_format(billDate, "%%Y%%m") = "%s"''' %(month)
        cnt = dbmngr.dbmngr.execute(sql)
        if cnt==0:
            return 0
        
        res = dbmngr.dbmngr.cursor.fetchall()
        # print res
        # print res[0]
        # print res[0][0]
        return res[0][0]

    @classmethod
    def getExpenseList(cls, subject=None, dateStart=None, dateEnd=None, 
                        delete=0, queryDate='update_time'):
        
        if delete:
            filter = 'where a.deleter is not null'
        else:
            filter = 'where a.deleter is null and '
            if subject:
                filter += ' a.subject=%d and'%subject
                
            if dateStart:
                filter += ' a.%s between "%s" and "%s" and'%(queryDate, dateStart, dateEnd)
            
            filter = filter[:-3] if subject or dateStart else ''
                        
        sql = '''select a.id,a.date,f.briefName,a.amount,b.name,a.memo,
                        c.name,a.create_time,d.name,a.update_time,
                        e.name,a.delete_time,a.delete_reason
                 from expense a left join account_subject b on a.subject = b.id
                                left join staff c on a.creater = c.id 
                                left join staff d on a.updater = d.id
                                left join staff e on a.deleter = e.id
                                left join account f on a.account_id = f.id
                %s''' %(filter)
                
        #print sql
        dbmngr.dbmngr.execute(sql)
        result = dbmngr.dbmngr.cursor.fetchall()
        #print result
        return result
    
    @classmethod
    def getObj(cls, keyVal):
        '''查找对象，只能通过key查找，并且为了避免同步问题，必须从数据库读取
        '''
        sql = 'select %s from %s where id=' %(','.join(cls.m_fieldNames), cls.tbl)
        sql += '%s'
        param = (keyVal)
        cnt = dbmngr.dbmngr.execute(sql, param)
        if cnt <=0:
            print u'查找不到id为%s的数据' %keyVal
            return None
        
        result = dbmngr.dbmngr.cursor.fetchall()
        print result
        
        obj = cls()
        for i,val in enumerate(result[0]):
            obj.__dict__[cls.fields[i][0]] = val
        return obj
        
    @classmethod
    def __fields_to_sql(cls, fields):
        
        sql = ''
        for field,val in fields.items():
            if val is not None:# include:'',0
                sql += field +'=' 
                if (isinstance(val, str) | 
                    isinstance(val, unicode)|
                    isinstance(val, datetime.date)):
                    sql += "'%s'," %(val)
                else:
                    sql += "%s," %(val)
            else:
                sql += field+'=NULL,'

        return sql[:-1]
    
        
    @classmethod
    def update_item(cls, keyVal, fields):
        '''update
        
            fields - {'field':val,}
        '''
        
        sql = 'update %s set %s where id=' %(cls.tbl, cls.__fields_to_sql(fields))
        sql += '%s'
        
        param = (keyVal)
        return dbmngr.dbmngr.execute(sql, param)           
    
    def __update(self, fields=None):
        self.update_item(self.id, {field:self.__dict__[field] for field,defval in self.fields})
                 
    @classmethod
    def delete_item(cls, id, reason=''):
        cls.update_item(id, {'deleter':config.Cfg.getCurUser().getKeyVal(),
                            'delete_reason':reason, 
                            'delete_time':datetime.datetime.now()})
    
    
        
    def delete(self, reason=''):
        self.deleter = config.Cfg.getCurUser().getKeyVal()
        self.delete_time = datetime.datetime.now()
        self.delete_reason = reason
        self.update()
        
    def save(self):        
        
        if not self.id:
            self.create_time = datetime.datetime.now()
            self.update_time = datetime.datetime.now()
            self.creater = config.Cfg.getCurUser().getValue(CDataStaff.COL_ID) 
            self.__insert()
        else:
            self.update_time = datetime.datetime.now()
            self.updater = config.Cfg.getCurUser().getValue(CDataStaff.COL_ID)
            self.__update() 
        
        return self
     
    def __insert(self):
        '''Note:增、删、改都要考虑到其他相关表的信息是否需要修改'''
        
        valList = []
        strField = strVal = ''
        for field,val in self.fields:
            if field == 'id':
                continue
            strVal += '%s,'
            strField += field + ','
            valList.append(self.__dict__[field])
            
        sql = 'insert into %s (%s) values(%s)' %(self.tbl, strField[:-1], strVal[:-1])
        
        #print (sql, self.m_fieldVals)
        cnt = dbmngr.dbmngr.execute(sql, valList)

        #if (cnt > 0):
            #self.__class__.m_dataList.append(self)
            #self.populateExtra()
            #self.notify('insert')
            
        return cnt
    
    
        
    def update(self, cols = []):
        '''设置好数据后，执行该函数以便更新数据库
            cols - [COL_xx,]
        '''
        import datetime
        
        #self.populateExtra()
        #self.notify('update')
        
        if (len(cols) == 0):
            #update all
            cols = [i for i in range(len(self.m_fieldNames))]
        
        sql = 'update  ' + self.tbl + ' set '    
        for i in cols:
            # 最初是只有update非None字段，后来有需要将非None设置成None，所以
            # 该段代码也可以改为
            if (self.m_fieldVals[i] is not None):
                sql += '%s=' %(self.m_fieldNames[i])
                #print type(self.m_fieldVals[i])
                if (isinstance(self.m_fieldVals[i], str) | 
                    isinstance(self.m_fieldVals[i], unicode)|
                    isinstance(self.m_fieldVals[i], datetime.date)):
                    sql += "'%s'," %self.m_fieldVals[i]
                else:
                    sql += '%s,' %(str(self.m_fieldVals[i]))
            elif (self.m_fieldDefVals[i] is None):
                sql += self.m_fieldNames[i]+'=NULL,'
            else:
                sql += '%s=%s,' %(self.m_fieldNames[i], str(self.m_fieldDefVals[i]))
                    
        sql = sql[:-1] + ' '
        sql += 'where %s=' %(self.m_fieldNames[self.COL_KEY])
        sql += '%s'
        param = (self.m_fieldVals[self.COL_KEY])
        return dbmngr.dbmngr.execute(sql, param)
    
class CDataVersionTable(CDataModel):
    tbl = u'version_table'
    fields = [('id', -1), ('table', ''), ('update_time', None),]
    m_fieldNo = {}
    m_fieldNames = []
    m_fieldCNNames = []
    m_dataList = []
    
    autoId = True
    fi_key = 0
    COL_KEY = 0
    
    def __init__(self):
        self.isNew = True
        for field,val in self.fields:
            self.__dict__[field] = val
            
    @classmethod
    def factory(cls):
        return cls()
    
    @classmethod
    def isOutofDate(cls, table):
        sql = 'select update_time from version_table where table=%s'%(table)
        cnt = dbmngr.dbmngr.execute(sql)
        if cnt==0:
            return False
        
        result = dbmngr.dbmngr.cursor.fetchall()
        
        
def dataModelInit():
    CDataProduct.refreshDataList()
    CDataCategory.refreshDataList()
    CDataKeyword.refreshDataList()
    CDataDelivery.refreshDataList()
    CDataBackup.refreshDataList()
    CDataCompany.refreshDataList()
    CDataStaff.refreshDataList()
    CDataAccount.refreshDataList()
    #CDataAccountDetail.refreshDataList()
    
    #CDataSaleOrder.refreshDataList()
    CDataSaleOrder.initClsData()
    #CDataVoucher.refreshDataList()
    #CDataOperator.refreshDataList()
    
    CDataCustomer.initClsData()
    #CDataSupplier.refreshDataList()
    CDataSupplier.initClsData()
    
    #CDataPurOrder.refreshDataList()
    CDataPurOrder.initClsData()
    
    #CDataPurDetail.refreshDataList()
    
    CDataEvent.refreshDataList()
    
    #CDataTableSale.refreshDataList()
    CDataTableSale.initClsData()
    
    CDataVoucher.initClsData()
    CDataVoucherSupplier.initClsData()
    
    CDataCustomerContact.initClsData()
    CDataCustomerType.initClsData()
    CDataCustomerLabel.initClsData()
    CDataCustomerLabeling.initClsData()
    CDataCustomerRegion.initClsData()
       
    CDataVersion.initClsData()
    
    CDataRecord.initClsData()
    
    CDataPeer.initClsData()
    CDataPeerLend.initClsData()
    CDataPeerFund.initClsData()
    
    CDataLog.initClsData()
    CDataTip.initClsData()
    
    #CDataSaleOrderTemp.refreshDataList()
    #CDataSaleOrderTemp.initClsData()
    
    CDataProLabel.refreshDataList()
    # for Test,  need sync while running
    CDataProLabel.populateClsData()

    CDataOrderState.initClsData()
    
    CDataAccountSubject.initClsData()
    
    CDataDeliveryPlan.initClsData(True)
    CDataDeliveryRoute.initClsData(True)
    CDataDeliveryRouteRegion.initClsData()
    CDataDeliveryState.initClsData(True)
    
    CDataTruck.initClsData(True)
    CDataWarehouse.initClsData(True)
    
    CDataExpense.initClsData()


    CDataAccountCheck.initClsData()

    CDataAssets.initClsData()
    CDataIncomeStatement.initClsData()
    CDataCashFlow.initClsData()
    CDataProfitProduct.initClsData()
    CDataMallProfit.initClsData()

    
    # 开始observer事件通知
    CDataModel.enableObserver = True